How to dynamically get data upto current date

To dynamically filter your data up to the current date in Power Query, you can build a dynamic calendar based on your Fact Sales table.

1. Create a Start Date query:

   • Reference your Fact Sales table.

   • Keep only the column containing the start date (e.g., OrderDate), and remove all others.

   • Change the column type to Date.

   • Remove duplicates, then extract the earliest date using the "Drill Down" feature.

   • Rename this query to StartDate.

2. Create an End Date query:

   • Reference your Fact Sales table again.

   • Keep only the end date column (e.g., OrderDate or InvoiceDate), and remove all others.

   • Convert the column type to Date.

   • Remove duplicates, then extract the latest date using "Drill Down".

   • Rename this query to EndDate.

3. Generate the calendar:

• In a new blank query, use the following formula to create a list of dates:
  = {Number.From(StartDate)..Number.From(EndDate)}
  
  

• Press Enter. This will generate a dynamic list of dates from your earliest to latest available in the fact table.

• You can then transform this list into a proper calendar table and add additional columns (like Year, Month, etc.) as needed.

This approach ensures your calendar always aligns with the available data in your Fact Sales table, dynamically adjusting to include dates up to the current maximum.