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 !
I need to display data on a histogram for the last 3 years, knowing that the user has selected the max year.
I have a dataset "FACTS" showing sales by product for each year.
I also have a table "DIMENSION" , with only selectable years. This table is linked to the previous one by the YEAR variable, but the relationship is inactive.
I made a segment allowing a simple selection of a year in a drop-down list.
I have a measure, calculating the sum of sales.
I have a histogram displaying this measure by year.
I want to add a filter to this visual to show only the last 3 years. For example, if the user chose 2020, we should see 2020, 2019 and 2018. As a result, the data must be displayed if and only if : YEAR - SELECTEDVALUE in (0,1,2)
But I don't know how to write this in DAX.
Can anyone help me?
Thanks
Solved! Go to Solution.
Hi @AilleryO ,
Thank you for your help, unfortunately it didn't work. Finally, I worked around the problem by using an additional table linking the year to be selected to the years to be viewed.
Regards,
QLB
Hi @AilleryO ,
Thank you for your help, unfortunately it didn't work. Finally, I worked around the problem by using an additional table linking the year to be selected to the years to be viewed.
Regards,
QLB
Hi,
It could have been easier with real dates, since we could work with time Intelligence, but if you don't have a real date in your data, you can calculate the previous year.
Measure =
Var SelectedYear = MAX ( [Year] )
Var PrevYear = SelectedYear-1
Var PrevPrevYear = PrevYear-1
RETURN
CALCULATE( YourMeasure , Year IN { SelectedYear , PrevYear , PrevPrevYear } )
Shoud do the trick, do not hesitate to tell us if it works..
It could be used as well with a FILTER or CALCULATETABLE...
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 |
---|---|
47 | |
24 | |
20 | |
15 | |
13 |
User | Count |
---|---|
51 | |
46 | |
39 | |
19 | |
19 |