Filter Date Table To MIN and MAX of fact table

[boyddt_mn]

Hello and thanks in advance.

I have a corporate date table in our data lake that has all of our corporate required fields (Fiscal Year, 4-4-5, etc...) that runs from 2015 through 2037. Our division's data only goes back to 2019, we generally only report back 12 months and obviously anything beyond today is a waste. Is there a way I can filter this table to show either 1) date range I specify 2) the min and max of our fact tables?

[rajendraongole1 · Accepted solution]

Hi @boyddt_mn  - Yes, you can filter your corporate date table in Power BI to show only the relevant date range based on your reporting needs or the date range of your fact tables. 

you can do this activity in pq editor as like 

Filter Date to include only rows within your desired range, e.g., Date is greater than a specific start date and less than today.

Table.SelectRows(financials, each [Date] >= Date.AddYears(DateTime.LocalNow(), -1) and [Date] <= DateTime.LocalNow())

replce with CorporateDateTable name instead of financials. Use a slicer or calculated table to dynamically filter the date table.

FilteredDateTable =
FILTER(
CorporateDateTable,
CorporateDateTable[Date] >= TODAY() - 365 &&
CorporateDateTable[Date] <= TODAY()
)

Hope this helps.

[boyddt_mn]

The DAX code worked spectacularly! Thank you.