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 all 🙂
I need to filter an amount within the last 12, 24 and 36 months from the latest date my date filter is set to.
My date filter is currently set to 01/01/2022 - 30/04/2022, so the visual should filter the amount between these dates;
12 months: 01/05/2021 - 30/04/2022
24 months: 01/05/2020 - 30/04/2022
36 months: 01/05/2019 - 30/04/2022
How is it possible to achieve this? None of the Relative Date settings on the filter are suitable.
Please advise!
Many many thanks
Solved! Go to Solution.
@HB13 , If you do not need to show date related column on the axis you can try like
Rolling 12 Sales =
var _max = maxx(allselcted(date),date[date]) // or today()
var _min = date(year(_max), month(_max)-12,1)
return
CALCULATE(SUM(Sales[Sales Amount]),filter(all(date), date[date] <=_max && date[date] >=_min))
or
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))
But if select 1 month and want to show 12 month's trend then the slicer should be on an independent date table
//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -12) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))
Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI
@HB13 , If you do not need to show date related column on the axis you can try like
Rolling 12 Sales =
var _max = maxx(allselcted(date),date[date]) // or today()
var _min = date(year(_max), month(_max)-12,1)
return
CALCULATE(SUM(Sales[Sales Amount]),filter(all(date), date[date] <=_max && date[date] >=_min))
or
Rolling 12 = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))
But if select 1 month and want to show 12 month's trend then the slicer should be on an independent date table
//Date1 is independent Date table, Date is joined with Table
new measure =
var _max = maxx(allselected(Date1),Date1[Date])
var _min = eomonth(_max, -12) +1
return
calculate( sum(Table[Value]), filter('Date', 'Date'[Date] >=_min && 'Date'[Date] <=_max))
Need of an Independent Date Table:https://www.youtube.com/watch?v=44fGGmg9fHI
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 |
---|---|
114 | |
99 | |
83 | |
70 | |
61 |
User | Count |
---|---|
149 | |
114 | |
107 | |
89 | |
67 |