Forum Discussion

mfaber's avatar
mfaber
Frequent Visitor
9 years ago
Solved

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

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 ...
Need Help
Tutorial Requests
  • MattAllington's avatar
    MattAllington
    9 years ago

    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)

     

MattAllington's avatar
MattAllington
Community Champion
9 years ago

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)

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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.

  • mfaber's avatar
    mfaber
    Frequent Visitor
    9 years ago

    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. :) )