Forum Discussion

tomtang's avatar
tomtang
Helper III
6 years ago
Solved

Dynamic Waterfall Chart toggle between Budget/Forecast/Budget

Dear all,   I am trying to create a dynamic waterfall chart allows the user to toggle between Forecast/Budget/Last Year comparing to this year Actual.   Sample as below chart.     Foreca...
Need Help
v-chuncz-msft's avatar
v-chuncz-msft
Community Support
6 years ago

tomtang 

 

You may add CALCULATETABLE.

CALCULATETABLE ( VALUES ( Fact_Sample[Category_R] ), ALL ( 'Date' ) ),

 

  • tomtang's avatar
    tomtang
    Helper III
    6 years ago

    v-chuncz-msft 

     

    Thanks for the response.

     

    In my actual case, it's a bit more complicated than the sample, since I add the other dimension (column) in the waterfall chart (which is "Segment")

     

    But I finally achieve my expectation with the following code from your suggestion, just share if someone else might need it.

    (I still not quite sure how does it works, need some more study on CALCULATETABLE)

     

    CALCULATE([USDsum],
    FILTER(
    ALL(Dim_Calendar[FullDateAlternateKey],Dim_Calendar[Year],Dim_Calendar[Month]),
    Dim_Calendar[Month]<=VALUES(Dim_Calendar[Month]) &&
    (Dim_Calendar[Year] = VALUE(Dim_Calendar[Year])-1 || Dim_Calendar[Year] = VALUE(Dim_Calendar[Year]))
    ),
    EXCEPT(CALCULATETABLE(VALUES(Fact_BUCONSOL_Union[Category_R]),ALL(Dim_Calendar[FullDateAlternateKey],Dim_Calendar[Year],Dim_Calendar[Month])),{ "B","F"})
    )
     
     
    v-chuncz-msft Thanks again and cheers!