Date.IsInQuarterToDate, Date.IsInMonthToDate and D… – Microsoft Fabric Community

Use Case – Power Query’s M language provides a rich collection of functions and its Date functions library offer a varieties of functions to perform multitude of date related tasks. One of the functions offered by Power Query’s M language is Date.IsInYearToDate function. This function will give True or False depending on whether date is within Year to Date range. Hence, if today’s date is 25-Mar-22, then dates from 1-Jan-22 to 25-Mar-22 will be giving True while any date not falling within this date range will give False. Hence, 26-Mar-22 will give the result of False.

Date.IsInYearToDate is a great function and finds tremendous use in handling many business scenario where you are asked to present things like YTD Sales, YTD Cost, YTD Profit, YTD Attrition and so on.

But Power Query’s M Language doesn’t have Date.IsInQuarterToDate, Date.IsInMonthToDate and Date.IsInWeekToDate functions which also have tremendous business needs. So, how to answer questions like QTD Sales, MTD Attrition, WTD Enquiries

 

Solution –  Following simple formulas can be used in this scenario

Date.IsInQuarterToDate

 

 

= Date.IsInCurrentQuarter(Date.From([DateColumn])) and Date.From([DateColumn])<=Date.From(DateTime.FixedLocalNow())

 

 

Date.IsInMonthToDate

 

 

= Date.IsInCurrentMonth(Date.From([DateColumn])) and Date.From([DateColumn])<=Date.From(DateTime.FixedLocalNow())

 

 

Date.IsInWeekToDate

 

 

= Date.IsInCurrentWeek(Date.From([DateColumn])) and Date.From([DateColumn])<=Date.From(DateTime.FixedLocalNow())

 

 

In the above formulas, you will need to replace DateColumn with the column name against which you will be checking your dates.

The above formulas will work for all 3 date types – date, datetime, or datetimezone

To see the working of these, Open a blank query – Home – Advanced Editor – Remove everything from there and paste the below code to test. You can set TestYear to any year.  Currently I have set it to 2022.

 

 

 

let
    TestYear = 2022,
    ListOfDates = List.Dates(#date(TestYear,1,1), Duration.Days(#date(TestYear,12,31)-#date(TestYear,1,1))+1, #duration(1,0,0,0)),
    #"Converted to Table" = Table.FromList(ListOfDates, Splitter.SplitByNothing(), {"DateColumn"}, null, ExtraValues.Error),
    #"Changed Type" = Table.TransformColumnTypes(#"Converted to Table",{{"DateColumn", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Date.IsInQuarterToDate", each Date.IsInCurrentQuarter(Date.From([DateColumn])) and Date.From([DateColumn])<=Date.From(DateTime.FixedLocalNow())),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Date.IsInMonthToDate", each Date.IsInCurrentMonth(Date.From([DateColumn])) and Date.From([DateColumn])<=Date.From(DateTime.FixedLocalNow())),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Date.IsInWeekToDate", each Date.IsInCurrentWeek(Date.From([DateColumn])) and Date.From([DateColumn])<=Date.From(DateTime.FixedLocalNow()))
in
    #"Added Custom2"

 

 

—- End of Article —-