Waterfall chart with monthly comparision to previous year

[Oliver_CH]

Dear all

I have been fighting for several hours to create a waterfall chart with the specifications I need.

I do have a report with just a date slicer (selection by month) and a waterfall chart. The waterfall chart shows the net sales changes by product to the previous year. The dashboard user is interested in analyzing single months (e.g. January 2022) as well as the cumulative total (year-to-date).

As the user selects a single month (e.g. January 2022), he wants to see how the monthly net sales has changed by products to the previous year month (January 2021) in the waterfall chart. I understand that the date slicer passes its selection, January 2022, to the x-axis of the waterfall chart and I therefore need a separate, disconnected date table to circumvent this effect.

I created the disconnected date table as well as a measure to get the result I want. The measure works for the month comparison (January 2022 versus January 2021). However, if I want analyse the cumulative total by selecting two or more months in the date slicer (e.g. January 2022 and February 2022), the measure is not working anymore and the waterfallchart shows me nothing.

I would like to achieve that the measure still works by selecting multiple months and not just one month. Can you help me with this?

This is my data model


This is my measure that shows the month comparison in the waterfall chart (e.g. February 2022 vs. February 2021):


And this is the result I want with this measure by just selecting one month:


However, if I select multiple months for a cumulative (year-to-date) total, the measure is not working anymore:

Thank you for your support.

Best regards,
Oliver

[jmazzola31]

Can anyone explain to me how the seperate table is formatted and why it allows for this functionality now? I am trying to accomplish the same thing in one of my Power BI reports.

[v-chenwuz-msft]

Hi @Oliver_CH ,

The reason why your measure does not work when you selected multiple months is the values() return result with two values and "=" of the your first filter result is null. so there is no result for calculate.

So, maybe you can try "in" unless "=" but im not sure it is right. Or you can try DATEADD().

Or this following code:

Net sales waterfall =
CALCULATE (
    SUM ( Facttable[Net Sales] ),
    ALLSELECTED ( DateTable[Year] ),
    DATEADD ( DateTable[Date], -1, YEAR ),
    USERELATIONSHIP ( DateTable[Date], sepDateTable[Date] )
)

If this does not work, please share your pbix file without sensitive data, if you need more help.

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.

[Oliver_CH · Accepted solution]

Eureka – I have found it!

I put the focus in the code on the month number (i.e. 1 = January, 2 = February, etc.) from the date table. With this both years get selected as the months always have the same month number.

This is the code:

Waterfall Net Sales = 

var Month_Nr = VALUES(DateTable[Month_Number])

return

CALCULATE(
            sum(Facttable[Net Sales]),
                FILTER(ALL(DateTable),
                        DateTable[Month_Number] IN Month_Nr),
            USERELATIONSHIP(DateTable[Date],sepDataTable[Date])
)

As the dataset only has two years (rolling two years) this code works. If someone out there has a dataset with 2+ years, please consider the year selection in the code. Otherwise the code will return the month numbers of all years. 

Thank you all for your help!

[Oliver_CH]

Hi there
Thank you for your reply, which is greatly appreciated. 

I tried your code. However, the waterfall chart only shows the current year, which is the year selected from the slicer.



As the x-axis is direct linked to the slicer, I somehow need to bypass the selected year. 

I will upload the pbix file later.
Best regards,
Oliver

[lbendlin]

From what I can see you 

- use VALUES inside CALCULATE  - that will have no effect. 

- use REMOVEFILTERS after specifying a filter on the same field. That will again nullify the intent.

You may want to think about using TREATAS instead of messing with the relationships.