Measure to get both selected month of the current year, and the same month from the previous year

[wl2020]

I am trying to create a waterfall chart that looks like this, which compares the results of last year and this year 

The datatable in question is a vertical table that with a label, a value, and date column. 

It is connected to a date table, and I have a drop down slicer with the dates. When a user choose a date, I want to get the results of the date chose, as well as the same date last year, sort of like this

I have created a DAX formula, 

Wasserfall GJ VJ = VAR MonNum = 1 RETURN CALCULATE(SUM(Wasserfall[Value])/1000, Wasserfall[Monat]=MonNum, ALL(ln_dim_period[Jahr1]), ALL(ln_dim_period[Name]))

Which works, but only if I manually enter a number. If I change the number to something dynamic such as SELECTEDVALUE(Month), it would only pull up the information of the month selected, and not that of the same month from the previous year. Does anyone have any idea how to fix the formula so it responds to the date slicer?

[Jihwan_Kim]

Hi, @wl2020 

If it is OK with you, please share your sample pbix file, then I can try to come up with desirable measures for your report.

By only seeing the screenshot of your report, in my opinion, no need to write VAR MonNum=1.

Instead,

VAR  currentmonth = Max (currentmonthnumbercolumn in your date table), then fix some of the measures in other area.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

[wl2020 · Accepted solution]

Thanks for the suggestion: I actually managed to solve it by making a sligh modification to your formula: 

Wasserfall GJ VJ = VAR MonNum = CALCULATE(MAX(ln_dim_period[Monat1]), ALLSELECTED(ln_dim_period)) RETURN CALCULATE(SUM(Wasserfall[Value])/1000, Wasserfall[Monat]=MonNum, ALL(ln_dim_period[Jahr1]), ALL(ln_dim_period[Name]))