Forum Discussion

tomtang's avatar
tomtang
Icon for Helper III rankHelper 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
Icon for Community Support rankCommunity Support
6 years ago

tomtang 

 

You may add CALCULATETABLE.

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

 

tomtang's avatar
tomtang
Icon for Helper III rankHelper 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!