Using Waterfall Charts

[Voose]

After watching the learning videos and resources around waterfall charts I realised that in the video (https://powerbi.microsoft.com/en-us/guided-learning/powerbi-learning-3-8-create-waterfall-funnel-cha...) he uses a vairance. At present in my Waterfall chart im using a total so it only ever looks positive. 

What calculation would I use to compare Fiscal Quarter 2016 1 with fiscal quarter 2017 1 and if the latter is lower then show a decline?

Thanks in advance!

[klabir]

Let me introduce you today the Ultimate Waterfall Chart, a Custom Visual which will be soon available in the gallery. Lets have a more detailed look on my first example I have prepared. It’s not a final dashboard, it just has the charts or features on it I want to explain. The Charts follow somehow the IBCS(R) Standard.

http://dataviz.boutique/2017/03/25/live-demo-powerbi-ultimate-waterfall-custom-visual/

[v-ljerr-msft]

Hi @Voose,

---

What calculation would I use to compare Fiscal Quarter 2016 1 with fiscal quarter 2017 1 and if the latter is lower then show a decline?

---

Waterfall charts are typically used to show changes in a particular value over time. So I don't think you can use it to compare Fiscal Quarter 2016 1 with fiscal quarter 2017 1.

In this scenario, I would suggest you create a measure to calculate the variance between a Fiscal Quarter and its Last Year Fiscal Quarter first, then show the measure on a clustered column chart with your Date column. The formulas below is for your reference.

Total Units = SUM(Sales[Units])

LY Total Units = CALCULATE([Total Units],SAMEPERIODLASTYEAR('Date'[Date]))

Total Units Var = [Total Units] - [LY Total Units]

Regards

[Voose]

Hi @v-ljerr-msft,

Thanks for your swift reply, I agree with you about waterfall charts and the below visual is very useful. Its made me confirm that I want to use a Waterfall chart. I have started to use the measures that you have outlined below. 

Total Units measure for me is = Total Sales = sum(Opportunity[Total ARR Converted])

LY Total Units = LY Total Sales = calculate([Total Sales],SAMEPERIODLASTYEAR(Opportunity[CloseDate].[Date]))

And my variance is = Total Sales Var = [Total Sales]-[LY Total Sales]

Been trying to use the variance for a waterfall chart but no luck yet, any advice? 🙂

Thanks

[v-ljerr-msft · Accepted solution]

Hi @Voose,

As most time intelligence functions(DAX) require an individual and continuous Calendar table to work correctly, you may need to create an individual Calendar table if you don't have yet(you can use CALENDAR function to create it).

Then you should be able to create a relationship between your "Opportunity" table and the Calendar table with the date column, and use the date column from the Calendar table in SAMEPERIODLASTYEAR function of the "LY Total Sales" measure.

LY Total Sales = CALCULATE ( [Total Sales], SAMEPERIODLASTYEAR ( Calendar[Date] ) )

Regards

[Voose]

@v-ljerr-msft Thank you for this, I didn't know the Calendar Function existed!! I wondered how everyone else was getting their dates to work!