A
- AccessControlEntry.ConditionToIdentities
- Access.Database
- ActiveDirectory.Domains
- AdobeAnalytics.Cubes
- AdoDotNet.DataSource
- AdoDotNet.Query
- AnalysisServices.Database
- AnalysisServices.Databases
- AzureStorage.BlobContents
- AzureStorage.Blobs
- AzureStorage.DataLake
- AzureStorage.DataLakeContents
- AzureStorage.Tables
B
- BinaryFormat.Binary
- BinaryFormat.Byte
- BinaryFormat.ByteOrder
- BinaryFormat.Choice
- BinaryFormat.Decimal
- BinaryFormat.Double
- BinaryFormat.Group
- BinaryFormat.Length
- BinaryFormat.List
- BinaryFormat.Null
- BinaryFormat.Record
- BinaryFormat.SignedInteger16
- BinaryFormat.SignedInteger32
- BinaryFormat.SignedInteger64
- BinaryFormat.Single
- BinaryFormat.Text
- BinaryFormat.Transform
- BinaryFormat.UnsignedInteger16
- BinaryFormat.UnsignedInteger32
- BinaryFormat.UnsignedInteger64
- BinaryFormat.7BitEncodedSignedInteger
- BinaryFormat.7BitEncodedUnsignedInteger
- Binary data
- Binary.ApproximateLength
- Binary.Buffer
- Binary.Combine
- Binary.Compress
- Binary.Decompress
- Binary.From
- Binary.FromList
- Binary.FromText
- Binary.InferContentType
- Binary.Length
- Binary.Range
- Binary.Split
- Binary.ToList
- Binary.ToText
- Binary.View
- Binary.ViewError
- Binary.ViewFunction
- Byte.From
C
- Cdm.Contents
- Character.FromNumber
- Character.ToNumber
- Combiner.CombineTextByDelimiter
- Combiner.CombineTextByEachDelimiter
- Combiner.CombineTextByLengths
- Combiner.CombineTextByPositions
- Combiner.CombineTextByRanges
- Comparer.FromCulture
- Comparer.Ordinal
- Comparer.OrdinalIgnoreCase
- Controlling byte order
- Csv.Document
- Cube.AddAndExpandDimensionColumn
- Cube.AddMeasureColumn
- Cube.ApplyParameter
- Cube.AttributeMemberId
- Cube.AttributeMemberProperty
- Cube.CollapseAndRemoveColumns
- Cube.Dimensions
- Cube.DisplayFolders
- Cube.MeasureProperties
- Cube.MeasureProperty
- Cube.Measures
- Cube.Parameters
- Cube.Properties
- Cube.PropertyKey
- Cube.ReplaceDimensions
- Cube.Transform
- Currency.From
D
- DateTime.AddZone
- DateTime.Date
- DateTime.FixedLocalNow
- DateTime.From
- DateTime.FromFileTime
- DateTime.FromText
- DateTime.IsInCurrentHour
- DateTime.IsInCurrentMinute
- DateTime.IsInCurrentSecond
- DateTime.IsInNextHour
- DateTime.IsInNextMinute
- DateTime.IsInNextNHours
- DateTime.IsInNextNMinutes
- DateTime.IsInNextNSeconds
- DateTime.IsInNextSecond
- DateTime.IsInPreviousHour
- DateTime.IsInPreviousMinute
- DateTime.IsInPreviousNHours
- DateTime.IsInPreviousNMinutes
- DateTime.IsInPreviousNSeconds
- DateTime.IsInPreviousSecond
- DateTime.LocalNow
- DateTime.Time
- DateTime.ToRecord
- DateTime.ToText
- Date.AddDays
- Date.AddMonths
- Date.AddQuarters
- Date.AddWeeks
- Date.AddYears
- Date.Day
- Date.DayOfWeek
- Date.DayOfWeekName
- Date.DayOfYear
- Date.DaysInMonth
- Date.EndOfDay
- Date.EndOfMonth
- Date.EndOfQuarter
- Date.EndOfWeek
- Date.EndOfYear
- Date.From
- Date.FromText
- Date.IsInCurrentDay
- Date.IsInCurrentMonth
- Date.IsInCurrentQuarter
- Date.IsInCurrentWeek
- Date.IsInCurrentYear
- Date.IsInNextDay
- Date.IsInNextMonth
- Date.IsInNextNDays
- Date.IsInNextNMonths
- Date.IsInNextNQuarters
- Date.IsInNextNWeeks
- Date.IsInNextNYears
- Date.IsInNextQuarter
- Date.IsInNextWeek
- Date.IsInNextYear
- Date.IsInPreviousDay
- Date.IsInPreviousMonth
- Date.IsInPreviousNDays
- Date.IsInPreviousNMonths
- Date.IsInPreviousNQuarters
- Date.IsInPreviousNWeeks
- Date.IsInPreviousNYears
- Date.IsInPreviousQuarter
- Date.IsInPreviousWeek
- Date.IsInPreviousYear
- Date.IsInYearToDate
- Date.IsLeapYear
- Date.Month
- Date.MonthName
- Date.QuarterOfYear
- Date.StartOfDay
- Date.StartOfMonth
- Date.StartOfQuarter
- Date.StartOfWeek
- Date.StartOfYear
- Date.ToRecord
- Date.ToText
- Date.WeekOfMonth
- Date.WeekOfYear
- Date.Year
- DB2.Database
- Decimal.From
- Diagnostics.ActivityId
- Diagnostics.Trace
- DirectQueryCapabilities.From
- Double.From
- Duration.Days
- Duration.From
- Duration.FromText
- Duration.Hours
- Duration.Minutes
- Duration.Seconds
- Duration.ToRecord
- Duration.TotalDays
- Duration.TotalHours
- Duration.TotalMinutes
- Duration.TotalSeconds
- Duration.ToText
E
F
G
H
I
L
- Lines.FromBinary
- Lines.FromText
- Lines.ToBinary
- Lines.ToText
- List.Accumulate
- List.AllTrue
- List.Alternate
- List.AnyTrue
- List.Average
- List.Buffer
- List.Combine
- List.ConformToPageReader
- List.Contains
- List.ContainsAll
- List.ContainsAny
- List.Count
- List.Covariance
- List.Dates
- List.DateTimes
- List.DateTimeZones
- List.Difference
- List.Distinct
- List.Durations
- List.FindText
- List.First
- List.FirstN
- List.Generate
- List.InsertRange
- List.Intersect
- List.IsDistinct
- List.IsEmpty
- List.Last
- List.LastN
- List.MatchesAll
- List.MatchesAny
- List.Max
- List.MaxN
- List.Median
- List.Min
- List.MinN
- List.Mode
- List.Modes
- List.NonNullCount
- List.Numbers
- List.Percentile
- List.PositionOf
- List.PositionOfAny
- List.Positions
- List.Product
- List.Random
- List.Range
- List.RemoveFirstN
- List.RemoveItems
- List.RemoveLastN
- List.RemoveMatchingItems
- List.RemoveNulls
- List.RemoveRange
- List.Repeat
- List.ReplaceMatchingItems
- List.ReplaceRange
- List.ReplaceValue
- List.Reverse
- List.Select
- List.Single
- List.SingleOrDefault
- List.Skip
- List.Sort
- List.Split
- List.StandardDeviation
- List.Sum
- List.Times
- List.Transform
- List.TransformMany
- List.Union
- List.Zip
- Logical.From
- Logical.FromText
Understanding the Date.IsInPreviousNWeeks function
The Date.IsInPreviousNWeeks function takes two parameters: the date to check and the number of weeks to go back. It returns a boolean value: true if the date falls within the previous N weeks, false otherwise. Here is an example of how to use this function:
let
Source = #table({“Date”}, {{“2022-02-01”}, {“2022-01-01”}, {“2021-12-01”}}),
PreviousWeeks = 4,
CustomFunction = (date as date) => Date.IsInPreviousNWeeks(date, PreviousWeeks),
AddColumn = Table.AddColumn(Source, “IsInPreviousWeeks”, each CustomFunction([Date])),
RemoveColumns = Table.RemoveColumns(AddColumn, {“Date”})
in
RemoveColumns
In this code, we create a table with a Date column, define the number of previous weeks to check (4), and create a custom function that uses the Date.IsInPreviousNWeeks function. We then add a new column to the table that applies this function to each value in the Date column, and finally remove the original Date column to keep only the boolean result.
Breaking down the M code
The M code behind the Date.IsInPreviousNWeeks function is straightforward:
(Date.ToWeekNumber(DateTime.LocalNow()) – Date.ToWeekNumber(date)) <= weeksAgo
This code calculates the week number of the current date (using DateTime.LocalNow()), subtracts the week number of the date to check (using Date.ToWeekNumber()), and compares the result to the number of previous weeks specified (weeksAgo). If the difference is less than or equal to weeksAgo, the function returns true, otherwise it returns false.
Customizing the function
If you need to modify the Date.IsInPreviousNWeeks function for your specific needs, you can do so by creating a custom function that uses the M code we just discussed. Here are some examples of how you can customize this function:
Change the reference date
By default, the Date.IsInPreviousNWeeks function uses the current date as the reference point. If you need to use a different date, you can modify the M code like this:
(Date.ToWeekNumber(#date(2022, 1, 1)) – Date.ToWeekNumber(date)) <= weeksAgo
In this code, we replace DateTime.LocalNow() with a specific date (#date(2022, 1, 1)). This will calculate the week number based on January 1, 2022, instead of the current date.
Check for future weeks
If you need to check if a date falls within the next N weeks instead of the previous N weeks, you can modify the M code like this:
(Date.ToWeekNumber(date) – Date.ToWeekNumber(DateTime.LocalNow())) <= weeksAhead
In this code, we swap the order of the week number calculations and change the comparison to weeksAhead. This will return true if the date falls within the next N weeks, instead of the previous N weeks.
Change the granularity
By default, the Date.IsInPreviousNWeeks function checks for weeks. If you need to check for a different granularity, such as days or months, you can modify the M code like this:
(Date.ToNumberOfDays(DateTime.LocalNow()) – Date.ToNumberOfDays(date)) <= (daysAgo 7)
In this code, we use the Date.ToNumberOfDays() function instead of Date.ToWeekNumber() to calculate the difference in days between the reference date and the date to check. We then multiply the daysAgo parameter by 7 to convert it from weeks to days.
The Date.IsInPreviousNWeeks function is a simple yet powerful M function in Power Query that allows you to check if a date falls within the previous N weeks. By understanding the M code behind this function and customizing it for your specific needs, you can take your date calculations to the next level.