M / Power Query Dynamic 25-Calendar-Months to Date Filter

[jengwt]

Hello, PBI community. I am working on a way to filter imported data for dynamic date ranges.

This particular attempt aims to filter for only the month to date and the preceding 24 calendar-months. This is what I have come up with, so far (see code below). It works, but does anyone have a more elegant or efficient way of doing this?

Note that today's data is not needed. We only want everything for the 25-calendar-months up through yesterday. For instance, on 9/22/2020 I need the first date to be 9/1/2018 and the last date to be 9/21/2020, and on 1/1/2021 I need the first date to be 12/1/2018 and the last date to be 12/31/2020.

This would be similar logic for Databricks Spark SQL and IBM Db2 SQL:

Spark SQL: TO_DATE(CreateTimestamp) >= ADD_MONTHS( DATE_ADD( DATE_ADD( CURRENT_DATE(), -1), -DAYOFMONTH( DATE_ADD(CURRENT_DATE(), -1)) +1), -24) AND TO_DATE(CreateTimestamp) < CURRENT_DATE()

Db2 SQL: CRT_DATE >= FIRST_DAY( CURRENT DATE - 1) - 2 YEARS AND CRT_DATE < CURRENT DATE

let
    Source = blah blah blah,
    navigation_step = blah blah blah,
    #"Filtered Rows" = Table.SelectRows(navigation_step, each Date.IsInPreviousNMonths([DayDate], 24) or Date.IsInPreviousNDays([DayDate], 30)),
    #"Removed Other Columns" = blah blah blah
in
    #"Removed Other Columns"

I previously tried using date add functions with DateTime.LocalNow, but that didn't work. I have since deleted it, but it was something like this:

#"Filtered Rows" = Table.SelectRows(navigation_step, each [DayDate] >= Date.AddMonths(Date.StartOfMonth(Date.AddDays(DateTime.LocalNow, -1)), -24)),

The data for this is not particularly important; this is more of a conceptual question. The data could be a calendar table or daily aggregations of data. One important consideration is that there may be too much data to be imported into the PBIX, so we want to only pull what is needed.

Thanks for your help!

[edhans]

@jengwt you are on the right track. Rather than have it directly in your M Code, consider this kind of query:

Create two blank queries. Call one varStartDate and one varEndDate (or whatever your naming convention is.

varStartDate = 

let
    Source = Date.StartOfMonth(Date.AddMonths(DateTime.Date(DateTime.LocalNow()), -24))
in
    Source

varEndDate 

let
    Source = Date.AddDays(DateTime.Date(DateTime.LocalNow()), -1)
in
    Source

Then, use this for your Between logic:

= Table.SelectRows(#"Changed Type", each [Date] >= varStartDate and [Date] <= varEndDate)

It is a little easier to read as you can see what date varStart/EndDate produces just by clicking on them in the Query listing, and the Table.SelectRows statement will fold if you are using this against any kind of server, meaning SQL Server, for example, will process the filter for you. If against an Excel file or CSV files, it won't matter from a performance perspective.

Just make sure both varStart/End Date  queries are not enabled to load.

[jengwt]

@edhans A problem that I'm having with this now is getting the time zones to display for the user in US Central Time. Any advice? I'm also exploring it in this thread.

[edhans]

See if this article helps @jengwt . About mid-way down there is a detaled discussion on how to show things in your local time zone using the following, which would be for the central TZ:

=DateTimeZone.SwitchZone(DateTimeZone.LocalNow(),-6 + varDSTOffset,0)

Please be sure to mark one or more of these responses as a solution. This thread is still showing as unresolved.

[jengwt]

@edhans normally I'm not one to create what I would call "variable tables" like those, but I like this idea! Those variables could be used in all of the queries and in the report itself, and you would only ever have to edit the two of them if the date requirements change. Thank you!

We do have a number of users who still like their spreadsheets and SharePoint sources, but we're moving towards a more modernized data-reporting model. This will be helpful from a best-practices standpoint.

[edhans]

Great @jengwt  - I have a group in Power Query called "Variables" and I still them all up there. Start and End date are common, but I have others. Some are in the form of lists (varTrackedVendors, or varVendorsActiveLTM) that can be manually or dynamically generated, then I use them in other places for filtering and whatnot.

It just makes everything easier to read, especially if you come back to it 6 months later and are debugging.

Hope the rest of your project runs smoothly!

[Greg_Deckler]

@jengwt Probably could use some sample data.

@ImkeF @edhans @HotChilli 

[jengwt]

Hello @Greg_Deckler. The data could be anything from a calendar table to daily aggregations. We just want to limit the date range on the import. One important consideration is that there may be too much data to be imported into the PBIX, so we want to only pull what is needed. I have added this and a sample screenshot to the OP.