Using slicer for two sheets or calculating change for waterfall chart on one page

[mfaber]

Hey,

so, I'm trying to make a waterfall chart for my table, but the problem is, I can't just make the changes column simply, because I have different categories and I want to see the changes for the same category, between months. (so for example, if in january I had 3 apples, 4 pears, in feb 4 apples, 6 pears, etc, then what I'd like to see is +1 for apples and +2 for pears for febr change. I'm using a slicer to choose category).

If I can make it a bit harder, I have many rows for one category and month, like in the pic below (example):

I tried a bit different approach myself, I made a table on another sheet where I used sumifs to calculate the sums, then with a simple formula I got my changes column. (pic below)

The waterfall chart could work with that nicely, but the problem is that, my slicer (which uses a column from another sheet) doesn't affect this one. Is there a way to filter data from two different sheets on one Power BI page?

Any help is greatly appreciated, thank you!

[MattAllington · Accepted solution]

You really need a calendar table with an ID column (integer that continuously advances by 1 for every month). WIth  this set up, you can create a lifetime to date calculation and subtract the lifetime to date from the previous lifetime to date for the prior month. Read my blog about calendar tables,http://exceleratorbi.com.au/power-pivot-calendar-tables/

then write a formula something like this. 

LTD = calculate (sum(data[value]),filter(all(calendar),calendar[ID] <= max(calendar[ID]))

chg vs last month = [LTD] - calculate (sum(data[value]),filter(all(calendar),calendar[ID] <= max(calendar[ID])-1)

[mfaber]

The day I posted this was the day I started using Power BI for the first time, so I needed some time to understand what you said, but I did it and it works, thank you!

(The pictures were just very dumbed-down examples, I'm using YYYYMM to identify the months, not this jan, feb, etc, of course. 🙂 )

[Anonymous]

@mfaber,

You may also want to think about what happens if you go from Dec to Jan and the year changes - using a calendar table will help deal with month-to-month calculations there.

And do you have (or can you use) daily granularity dates for each row rather than 'jan', 'feb' etc.?

@MattAllington has recommend a (monthly?) calendar table to solve the problem as specified.  IFF (and it may be a big one), the Data table was able to use daily rather than montlhy granularity, it might simplify the solution:

• Link to the Calendar table on the Date, rather than have to add a Month ID key (inc. year) to Data table
• Power BI should even create the table relationship automatically if the Date column name and type are the same
• Simpler looking measures using Time Intelligence functions (not for Direct Query though...?):

Total = SUM(Data[#])
Previous Month Total = CALCULATE([Total], PREVIOUSMONTH(Data[Date]))
Monthly Change = [Total] - [Previous Month Total] 

 You could then use the Year and Month from the Data table's Date hierarchy as the Waterfall chart category, and Monthly Change as the Y Axis.