Forum Discussion

BillyT_350's avatar
BillyT_350
Icon for Helper V rankHelper V
5 years ago

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. T...
Need Help
Show and Tell
Tips and Tricks
BillyT_350's avatar
BillyT_350
Icon for Helper V rankHelper V
5 years ago

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's avatar
edhans
Icon for Community Champion rankCommunity Champion
5 years ago

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!