Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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:
With that in mind, I have attached a modified PBIX.
'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.
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.
You can see that the movement bars are sorted in descending order of movement, with only 10 movements plus an 'Other' bar.
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
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:
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] )
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
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 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:
With that in mind, I have attached a modified PBIX.
'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.
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.
You can see that the movement bars are sorted in descending order of movement, with only 10 movements plus an 'Other' bar.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
100 | |
78 | |
76 | |
52 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |