Forum Discussion
Dynamic Yearly Date Ranges based on a Max Date (2)
Hi,
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 ))) |
Hi garynorcrossmmc ,
If you want to create date range based on the maximum date from another slicer. Calculated column is not an approach to you.
You can create a unassociated table for slicer:
Then use the following measure in your report:
Measure1 = SWITCH(SELECTEDVALUE('Table[RANGE]'),"-12Months",CALCULATE([Your Measure],DATEADD('HR DIM_MONTH'[Date (End of Month)],-1,YEAR)),"-24Months",CALCULATE([Your Measure],DATEADD('HR DIM_MONTH'[Date (End of Month)],-2,YEAR)),"-36Months",CALCULATE([Your Measure],DATEADD('HR DIM_MONTH'[Date (End of Month)],-3,YEAR)),[Your Measure])Or you can also use calculation group in your data model, please refer to https://www.sqlbi.com/articles/introducing-calculation-groups/
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
1 Reply
Hi garynorcrossmmc ,
If you want to create date range based on the maximum date from another slicer. Calculated column is not an approach to you.
You can create a unassociated table for slicer:
Then use the following measure in your report:
Measure1 = SWITCH(SELECTEDVALUE('Table[RANGE]'),"-12Months",CALCULATE([Your Measure],DATEADD('HR DIM_MONTH'[Date (End of Month)],-1,YEAR)),"-24Months",CALCULATE([Your Measure],DATEADD('HR DIM_MONTH'[Date (End of Month)],-2,YEAR)),"-36Months",CALCULATE([Your Measure],DATEADD('HR DIM_MONTH'[Date (End of Month)],-3,YEAR)),[Your Measure])Or you can also use calculation group in your data model, please refer to https://www.sqlbi.com/articles/introducing-calculation-groups/
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai