Forum Discussion
Dynamic slicer for waterfall chart
I would like to create a Actual vs. the selected comparable waterfall bridge chart. The selected comparable include quarter-on-quarter, year-on-year, budget, forecast which would be on a slicer to choose. The comparison would change based on the fiscal year and quarter I selected. Could you please advise how to do this? Thank you.
4 Replies
- amitchandak
Super User
The information you have provided is not making the problem clear to me. Can you please explain with an example.
Datesytd and totalytd allow user to specific date end to calculate YTD. Example
QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(('Date'[Date]))) Last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,QUARTER))) Last to last QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-2,QUARTER))) Next QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],1,QUARTER))) Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year))) YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31")) This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31")) Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31")) Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31")) Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31")) Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year)) Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd(dateadd('Date'[Date],-1,Year),-21,Day)) - Greg_Deckler
Community Champion
Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
V-lianl-msftCommunity Support
Hi jictang ,
You can refer to this thread:https://community.powerbi.com/t5/Desktop/Dynamic-Waterfall-Chart-toggle-between-Budget-Forecast-Budget/m-p/875498
Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.Hi Thanks for the reply. Yes I read that post but I am not sure how to combine both time / period (vs. prior year, vs, prior quarter) and scenario (vs. budget & vs. forecast) into one table for me to enable the slicer.
Sorry I am have new to Power BI, could you please explain further?
