Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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!
Solved! Go to 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)
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)
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. 🙂 )
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:
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.
User | Count |
---|---|
77 | |
74 | |
60 | |
60 | |
46 |
User | Count |
---|---|
109 | |
95 | |
86 | |
79 | |
61 |