I have a Sales query (fact table) that will frequently have a different range of dates. I need to build out a Calendar query that automatically adjusts based on the date range present in that Sales query. I don't want to have to manually enter a start/end date as this will frequently change. I'm still a little confused on how to do this or if it's possible. Ultimately, I'm using Power Query to build out the Calendar table that will be used in Power Pivot. So ideally, for any years present in the Sales query date range, I'd want to include all the individual dates beginning Jan. 1 and ending Dec. 31. Any tips or articles you could point me to would be appreciated.
Thanks!
Hi Amber,
Session 7.09 demonstrates how you can reference another query to generate a dynamic variable.
You need to use the same technique to create a dynamic date table. i.e.
1. Duplicate your sales query twice. Once for the start date and once for the end date you need in your calendar table. Name each query 'StartDate' and 'EndDate' accordingly.
2. Use the Transform tab > Date > Earliest/Latest to extract the first date from the StartDate query and the last date from the EndDate query. Tip: you'll want to convert the dates to the first and last dates in the year.
Then you can create a new blank query that uses the Number.From function to create a list of dates.
See example file attached.
Mynda
