Forum Discussion

Voose's avatar
Voose
Icon for Helper III rankHelper III
9 years ago
Solved

Using Waterfall Charts

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...
Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    9 years ago

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

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

     

    Regards

v-ljerr-msft's avatar
v-ljerr-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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's avatar
    Voose
    Icon for Helper III rankHelper III
    9 years ago

    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's avatar
      v-ljerr-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      9 years ago

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

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

       

      Regards

      • Voose's avatar
        Voose
        Icon for Helper III rankHelper III
        9 years ago

        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!