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, hopefully someone has an idea for me here!
So i have the below data structure:
Year table:
Year |
2019 |
2020 |
Month table:
Year | Month |
2019 | 2019-01 |
2019 | 2019-02 |
2019 | 2019-03 |
2019 | 2019-04 |
2019 | 2019-05 |
2019 | 2019-06 |
2019 | 2019-07 |
2019 | 2019-08 |
2019 | 2019-09 |
2019 | 2019-10 |
2019 | 2019-11 |
2019 | 2019-12 |
2020 | 2020-01 |
2020 | 2020-02 |
2020 | 2020-03 |
2020 | 2020-04 |
2020 | 2020-05 |
2020 | 2020-06 |
2020 | 2020-07 |
2020 | 2020-08 |
2020 | 2020-09 |
2020 | 2020-10 |
2020 | 2020-11 |
2020 | 2020-12 |
Revenue table:
Month | Revenue |
2019-01 | 100 |
2019-01 | 225 |
2019-05 | 200 |
2019-05 | 350 |
2020-01 | 600 |
2020-01 | 1250 |
What I want to do is add the Year.Year column into the Page filters for the page, then filter to the year 2019 and return this:
Month | Revenue |
2019-01 | 325 |
2019-02 | 0 |
2019-03 | 0 |
2019-04 | 0 |
2019-05 | 550 |
2019-06 | 0 |
2019-07 | 0 |
2019-08 | 0 |
2019-09 | 0 |
2019-10 | 0 |
2019-11 | 0 |
2019-12 | 0 |
I wrote a measure to try to achieve this:
RevenueMeasure =
VAR a = CALCULATE ( SUM ( Revenue[Revenue] ) )
RETURN
IF ( ISBLANK ( a ), 0, a )
However this gives me an incorrect result:
Is there a different way to write this measure? I have to filter on the Year.Year column for the Page filter. See below for PBIX file:
https://1drv.ms/u/s!AuiIgc_S9J5Jhbpy9TdV7XyccnDgdg?e=o3gxxR
Solved! Go to Solution.
Hi @ansa_naz ,
Just redo your measure to:
RevenueMeasure =
VAR a =
CALCULATE ( SUM ( Revenue[Revenue] ) )
VAR year_selection =
SELECTEDVALUE ( 'Year'[Year] )
RETURN
IF ( SELECTEDVALUE ( 'Month'[Year] ) = year_selection; a + 0; BLANK () )
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @ansa_naz ,
Just redo your measure to:
RevenueMeasure =
VAR a =
CALCULATE ( SUM ( Revenue[Revenue] ) )
VAR year_selection =
SELECTEDVALUE ( 'Year'[Year] )
RETURN
IF ( SELECTEDVALUE ( 'Month'[Year] ) = year_selection; a + 0; BLANK () )
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsBrilliant thank you!
There is no need of year table, join month year table with revenue on Month year and filter on year from that table it should work
I need the Year table to filter on other related tables, which are not relevant to the full question. Which is why I have to keep the data model as is, but still obtain required result. Any further help please?
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 |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |