Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
garynorcrossmmc
Advocate II
Advocate II

Dynamic Yearly Date Ranges based on a Max Date

Hi all,

A user has requested that 3 date ranges be built into a Power BI report as a slicer - 12 Months, 24 Months and 36 Months based on the maximum date from another slicer.  The date ranges should look back 12/24/36 months before the maximum date.  How can I build this onto my date table?  I've tried 

Lookback Period =
IF(
FILTER(
'HR DIM_MONTH',
'HR DIM_MONTH'[Date (End of Month)]>=MAX('HR DIM_MONTH'[Date (End of Month)])-365 &&
'HR DIM_MONTH'[Date (End of Month)]<=MAX('HR DIM_MONTH'[Date (End of Month)])
),
"12 Months",
IF(
FILTER(
'HR DIM_MONTH',
'HR DIM_MONTH'[Date (End of Month)]>=MAX('HR DIM_MONTH'[Date (End of Month)])-730 &&
'HR DIM_MONTH'[Date (End of Month)]<=MAX('HR DIM_MONTH'[Date (End of Month)])
),
"24 Months",
IF(
FILTER(
'HR DIM_MONTH',
'HR DIM_MONTH'[Date (End of Month)]>=MAX('HR DIM_MONTH'[Date (End of Month)])-1095 &&
'HR DIM_MONTH'[Date (End of Month)]<=MAX('HR DIM_MONTH'[Date (End of Month)])
),
"36 Months",
0
)))
 
But this returned an error.  How can I achieve this?  
1 ACCEPTED SOLUTION
v-yetao1-msft
Community Support
Community Support

Hi @garynorcrossmmc ,

Please correct me if I wrongly understood your question.

I create three measures by DATESINPERIOD DAX to return the values from last 12 months ,last 24 months and last 36 months .You can put the measures in report to filter the datas meet date condition but not as a slicer.

Last 12 months = CALCULATE(SUM('Table'[ID]),DATESINPERIOD('Calendar Date'[Date],MAX('Calendar Date'[Date]),-12,MONTH ))

Last 24 months = CALCULATE(SUM('Table'[ID]),DATESINPERIOD('Calendar Date'[Date],MAX('Calendar Date'[Date]),-24,MONTH ))

Last 36 months = CALCULATE(SUM('Table'[ID]),DATESINPERIOD('Calendar Date'[Date],MAX('Calendar Date'[Date]),-36,MONTH ))

 

Otherwise, I create a one to many relationship between data table and calendar table.

Ailsa-msft_0-1618536298221.png

Best Regards

Community Support Team _ Ailsa Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yetao1-msft
Community Support
Community Support

Hi @garynorcrossmmc ,

Please correct me if I wrongly understood your question.

I create three measures by DATESINPERIOD DAX to return the values from last 12 months ,last 24 months and last 36 months .You can put the measures in report to filter the datas meet date condition but not as a slicer.

Last 12 months = CALCULATE(SUM('Table'[ID]),DATESINPERIOD('Calendar Date'[Date],MAX('Calendar Date'[Date]),-12,MONTH ))

Last 24 months = CALCULATE(SUM('Table'[ID]),DATESINPERIOD('Calendar Date'[Date],MAX('Calendar Date'[Date]),-24,MONTH ))

Last 36 months = CALCULATE(SUM('Table'[ID]),DATESINPERIOD('Calendar Date'[Date],MAX('Calendar Date'[Date]),-36,MONTH ))

 

Otherwise, I create a one to many relationship between data table and calendar table.

Ailsa-msft_0-1618536298221.png

Best Regards

Community Support Team _ Ailsa Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

lbendlin
Super User
Super User

This only seems to produce the label for the selected period, not the actual filters.  Remove the ",0"  at the end to get rid of the error. Or - describe again what you are planning to achieve.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.