Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 ))) |
Solved! Go to Solution.
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
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
99 | |
82 | |
67 | |
63 |
User | Count |
---|---|
145 | |
111 | |
104 | |
84 | |
64 |