cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
fess440 Regular Visitor
Regular Visitor

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

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? 

1 ACCEPTED SOLUTION

Accepted Solutions
OwenAuger Super Contributor
Super Contributor

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

@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:image.pngData 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.image.png

    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.image.png

     

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

4 REPLIES 4
OwenAuger Super Contributor
Super Contributor

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

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:image.png

 

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




fess440 Regular Visitor
Regular Visitor

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

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 Regular Visitor
Regular Visitor

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

@OwenAugerany suggestions? Thanks for the extra consideration.

OwenAuger Super Contributor
Super Contributor

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

@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:image.pngData 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.image.png

    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.image.png

     

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

Helpful resources

Announcements
Exclusive LIVE Community Event #3 – Ask Arun Anything

Exclusive LIVE Community Event #3 – Ask Arun Anything

Join us in the third Triple A event!

Meet the 2020 Season 1 Power BI Super Users!

Meet the 2020 Season 1 Power BI Super Users!

It’s the start of a new Super User season! Learn all about the new Super Users and brand-new tiered recognition system.

January 2020 Community Highlights

January 2020 Community Highlights

Make sure you didn't miss any of the things that happened in the community in January!

Top Solution Authors
Top Kudoed Authors