Forum Discussion
Helper VM / Power Query Dynamic 25-Calendar-Months to Date Filter
Community ChampionBillyT_350 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.
BillyT_350edhans 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 BillyT_350 - 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!
- BillyT_350
@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 BillyT_350 . 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.