waterfall chart - shift x-axis to cumulative value at set date & show changes during time range

[fess440]

Hi, I have time series data and I have a date slicer that filters a waterfall chart. I'd like the waterfall chart to start at the cumulative value at start date instead of 0. I'd like it to then show changes (+/- values) during the set time period as the chart does usually. I'd like the series to end at the cumulative value at the end date. is this possible? 

[OwenAuger]

Hi @fess440 

Yes this can certainly be done. I have attached an example PBIX that can be tweaked to meet your exact requirements. The output looks like this:

The steps I followed to create this were:

1. Start with related tables Sales and Date. Sales contains a Customer column.
2. Create these measures:
   

   Sales Amount = 
   SUM ( Sales[Sales] )
   
   Cumulative Sales = 
   CALCULATE ( 
       [Sales Amount],
       DATESBETWEEN ( 'Date'[Date], BLANK(), MAX ( 'Date'[Date] ) )
   )
   
   Cumulative Sales on Boundary Dates = 
   VAR MinDate =
       CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
   VAR MaxDate =
       CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
   VAR SelectedDate =
       SELECTEDVALUE ( 'Date'[Date] )
   RETURN
       IF (
           OR ( SelectedDate = MinDate, SelectedDate = MaxDate ),
           [Cumulative Sales]
       )

3. Create a Waterfall visual with
   • Category = 'Date'[Date]
   • Breakdown = Sales[Customer]
     (could be any column of your choosing, but measures may need to be adjusted if it is also Date)
   • Y Axis = [Cumulative Sales on Boundary Dates]

The Cumulative Sales on Boundary Dates measure evaluates Cumulative Sales only on the overall min/max dates filtered, which ensures that the Waterfall visual shows "total" columns at those dates with movements in Cumulative Sales between those two dates.

This is an example and may well need to be tweaked to meet your needs.

Please post back if needed 🙂

Regards,

Owen

[fess440]

Hi @OwenAuger  thanks a lot for the detailed response!

Would it be possible to use date only in breakdown? I don't have a breakdown category aside from date. I am reworking the measures but didn't get far at all. I could attach the pbix file here but I don't have the option to do so. Let me know if you would like me to upload it somehwere if need be.

Do I absoloutely need a master date table? I'm not sure how it operates but the real dataset is not static and new records will be added in time.

Thanks again!

[fess440]

@OwenAugerany suggestions? Thanks for the extra consideration.

[OwenAuger · Accepted solution]

@fess440 yes I've taken a further look.

You can do something along these lines with a secondary date table.

This is because the built-in Waterfall visual won't allow the same field on Category & Breakdown. Also, we need to be able to distinguish between filters on Category and Breakdown fields.

Also on your Date table question - yes I would recommend a separate date table, with any date-related columns included (month, week etc). While it may not always be essential, it is best practice to include a separate dimension table for any entity that you filter or group by. The Date table can be made to update its range in line with your fact table.

Also a couple of things to note:

• The built-in Waterfall can handle up to 10 values in the Breakdown field, and the rest will be grouped into Other.
• The Breakdown field will be sorted by descending "movement" value, so there is no way to sort by Date for example.

With that in mind, I have attached a modified PBIX.

1. Data model updated as follows:Data model: 'Date Breakdown' is a copy of 'Date' with an inactive relationship with Date

   'Date Breakdown' is a copy of 'Date' with an inactive relationship between the Date columns of the two tables. 'Date Breakdown' is used for the Breakdown field on the Waterfall visual.

2. Define this measure to use on the Waterfall:
   

   Cumulative Sales on Boundary Dates v2 = 
   VAR MinDate =
       CALCULATE ( MIN ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
   VAR MaxDate =
       CALCULATE ( MAX ( 'Date'[Date] ), ALLSELECTED ( 'Date' ) )
   VAR SelectedDate =
       SELECTEDVALUE ( 'Date'[Date] )
   RETURN
       IF (
           // This condition required to restrict "Total Bars" to MinDate & MaxDate
           OR ( SelectedDate = MinDate, SelectedDate = MaxDate ),
           IF (
               // If Date Breakdown is not crossfiltered, then calculate Total Bar value
               NOT ISCROSSFILTERED( 'Date Breakdown'[Date] ),
               [Cumulative Sales],
               // Otherwise, calculate the Breakdown bar movement value
               // The movement is just the underlying non-cumulative measure ([Sales Amount] in this example) evaluated on a particular date range
               // The date range is restricted to dates > MinDate and <= MaxDate (KEEPFILTERS)
               // The measure is evaluated using the 'Date Breakdown' filter, ignoring the 'Date' filter (ALL & USERELATIONSHIP)
               IF (
                   SelectedDate = MaxDate,
                   CALCULATE (
                       [Sales Amount],
                       ALL ( 'Date' ),
                       USERELATIONSHIP( 'Date'[Date], 'Date Breakdown'[Date] ),
                       KEEPFILTERS ( DATESBETWEEN ( 'Date Breakdown'[Date], MinDate + 1, MaxDate ) )
                   )
               )
           )
       )

   The measure ensures that the total bars and movements are calculated correctly. Total bars are calculated as [Cumulative Sales] only when 'Date'[Date] is either the min/max. Movement bars are calculated as [Sales Amount] only when 'Date Breakdown'[Date] is between the min/max dates, and 'Date'[Date] is max.
3. Finally, create a Waterfall visual with Category = 'Date'[Date], Breakdown = 'Date Breakdown'[Date] and Y Axis = the measure above.

   You can see that the movement bars are sorted in descending order of movement, with only 10 movements plus an 'Other' bar.

4. I have set up the measure so that other 'Date Breakdown' columns can be used, for example End of Month.

I guess it would be nice if we could sort the Breakdown field. Maybe other Waterfall visuals out there could do this - I haven't tested yet.

Hopefully that's of some use 🙂

Regards,

Owen