How to dynamically get data upto current date

[han_rj]

I have a Fact sales table I would like to restrict data from 01 Jan 2025 to current date.

How to write the end date condition to always dynamically pick current date in Power Query

[Ilgar_Zarbali · Accepted solution]

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.

[mussaenda]

I am always filtering like this in power query. Instead of static filter, it will always filter like this.

 [Date] <= DateTime.LocalNow())

if there is no datetime and it is only date then you just need to wrap it with Date.From

[Date] <= Date.From(DateTime.UtcNow())