Cannot filter a measure - Help!

[ansa_naz]

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

Cheers for all help

[MFelix · Accepted 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 () )

[ansa_naz]

Brilliant thank you!

[amitchandak]

@ansa_naz 

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

[ansa_naz]

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?