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 have a data table which has multiple years in it (2019 to 2022).
I would like to have a measure that if I chose the slicer from Jan 2022 to May 2022 on my date filter it should sum the vlaues on 2019, 2020 and 2021 years each for only Jan to May on these years so I can compare it in a chart. It also should be fixed on these years no substracting them.
This's what I have but it shows blank data:
Please help
Solved! Go to Solution.
Hi @RexaZii93 ,
You need create an independent calendar table first which has no relationship with the fact table. The same as the following screenshot.
Then create a measure like this:
Measure =
CALCULATE (
SUM ( 'Fact table'[Values] ),
FILTER (
VALUES ( 'CALENDAR'[Date] ),
MONTH ( [Date] )
IN VALUES ( 'New calendar'[Month] )
&& DAY ( [Date] ) IN VALUES ( 'New calendar'[Day] )
)
)
Result:
Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @RexaZii93 ,
You need create an independent calendar table first which has no relationship with the fact table. The same as the following screenshot.
Then create a measure like this:
Measure =
CALCULATE (
SUM ( 'Fact table'[Values] ),
FILTER (
VALUES ( 'CALENDAR'[Date] ),
MONTH ( [Date] )
IN VALUES ( 'New calendar'[Month] )
&& DAY ( [Date] ) IN VALUES ( 'New calendar'[Day] )
)
)
Result:
Pbix in the end you can refer.
Best Regards
Community Support Team _ chenwu zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Please follow similar case in this post
Hello Tamer
The post only shows how to calculate the values in the past three years (-3) so if I want a certain year lets say 2019 I would have to change -3 to -4 every year.
what I would like is to calculate values on fixed year lets say 2019. Moreover, it only calculate the months I Pick on my callendar date, so if I chose Jan to July for 2022 it automatically do the same for 2019.
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 |
---|---|
112 | |
100 | |
76 | |
74 | |
49 |
User | Count |
---|---|
146 | |
108 | |
106 | |
90 | |
62 |