Skip to main content
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Find articles, guides, information and community news

Most Recent
Super User
Super User

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.IsInCurrentQuarter(Date.From([DateColumn])) and Date.From([DateColumn])<=Date.From(DateTime.FixedLocalNow())






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






= 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.




    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()))
    #"Added Custom2"



---- End of Article ----

Community Support
Community Support

Fiscal years/months/weeks are often mentioned when our calculations involve dates. However, a common calendar table cannot meet such needs. At this time, we need to create a fiscal calendar table. Certainly, it is relatively simple to create a fiscal calendar table by month or by week, and there are already many blogs about it. Therefore, I would like to share with you how to designate the first full week of a specific month as the start of the fiscal year.


Helpful resources

Join Blog
Interested in blogging for the community? Let us know.