cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Resolver I
Resolver I

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

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.

jengwt_0-1600871548717.png

Thanks for your help!

7 REPLIES 7
Highlighted
Super User IV
Super User IV

@jengwt Probably could use some sample data.

 

@ImkeF @edhans @HotChilli 


---------------------------------------

@ me in replies or I'll lose your thread!!!

I have a NEW book! 
DAX Cookbook from Packt
Over 120 DAX Recipes!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Super User III
Super User III

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

 



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted

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.

Highlighted

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

Highlighted

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!



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting
Highlighted

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

Highlighted

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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors