Wrange data for Waterfall Chart

[Anthony_W]

Hi All,

I am trying to wrangle my data into the format required for a waterfall chart. I can calculate a waterfall chart that just shows the difference in values from zero, but I would like to include the initial value in the chart. The table below has an example of my data.

Year-Quarter	xParameter	Difference	Year Quarter First	XParameter First
2020-Q1	38.21	38.21	2020-Q1	38.21
2020-Q2	36.99	-1.22	2020-Q1	0
2020-Q3	37.31	0.33	2020-Q1	0
2020-Q4	39.09	1.78	2020-Q1	0
2021-Q1	38.28	-0.81	2020-Q1	0
2021-Q2	39.03	0.75	2020-Q1	0
2021-Q3	39.55	0.51	2020-Q1	0
2021-Q4	38.41	-1.14	2020-Q1	0

If I plot a waterfall chart using Year-Quarter and the difference column as shown in the table, than the waterfall chart looks OK. However, if I introduce a filter for say a year such that Year-Quarter starts at 2021-Q1, then a difference of -0.81 is calculated for 2021-Q1 and the waterfall chart start from zero whereas I would prefer this to start at 38.28, and then follow the difference column then.

The Calculation for Difference is :

Difference =
CALCULATE (
    [xParameter]
        - CALCULATE ( [xParameter], PARALLELPERIOD ( DateTable[Date], -1, QUARTER ) )
)

I have tried to introduce an adjustment to account for the first xParameter value, but this always returns to the first value at 2020-Q1. For the example above, I would want the XParameter first value to show at 2021-Q1.

XParameter First =
IF (
    FIRSTNONBLANK ( DateTable[Year-Quarter], [Year-Quarter] )
        = FIRSTNONBLANK ( ALL ( DateTable[Year-Quarter] ), [Year-Quarter] ),
    [xParameter],
    0
)

The Year-Quarter is in a Date Table and filtering is done using other columns in the date table e.g. Year.

Any ideas of how to change the formulae to account for filtering the Year-Quarter Column? 

Thanks.

[v-yuta-msft]

@Anthony_W ,

I couldn't reproduce the senario below, if possible, could you please provide some screenshot(Do mask the sensitive data) or provide a sample file?

"If I plot a waterfall chart using Year-Quarter and the difference column as shown in the table, than the waterfall chart looks OK. However, if I introduce a filter for say a year such that Year-Quarter starts at 2021-Q1, then a difference of -0.81 is calculated for 2021-Q1 and the waterfall chart start from zero whereas I would prefer this to start at 38.28, and then follow the difference column then."

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anthony_W]

v-yuta-msft,

see link below for an example sample file.

Link to Waterfall example

The waterfall chart is OK when a year isn't selected. When a year is selected, the waterfall chart starts at zero, rather than the first ash value. Hope that makes sense!

Cheers,

Anthony