topic Re: Measure to get both selected month of the current year, and the same month from the previous year in DAX Commands and Tips

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

wl2020 — Thu, 01 Apr 2021 23:23:58 GMT

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?

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

Jihwan_Kim — Fri, 02 Apr 2021 04:23:35 GMT

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.

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

wl2020 — Fri, 02 Apr 2021 05:00:34 GMT

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]))