Waterfall Chart based on Status Variations and Dates

[arimoldi]

Hi,

I need to create a waterfall chart in Power BI based on a dataset like this:

REFERENCE_DATE	CODE	STATUS	SEVERITY
30/11/2024	A	ONGOING	LOW
30/11/2024	B	ONGOING	LOW
30/11/2024	C	ONGOING	HIGH
31/12/2024	A	ONGOING	LOW
31/12/2024	B	CLOSED	LOW
31/12/2024	C	ONGOING	HIGH
31/01/2025	A	ONGOING	LOW
31/01/2025	C	ONGOING	MEDIUM
31/01/2025	D	ONGOING	HIGH

and the expected final chart should be something like this:

So basically all the variations total count of CODEs, any variations of STATUS (every status to CLOSED) and any variations of SEVERITY (any decrease from from HIGH to LOW or MEDIUM) should be treated as -1, while the opposite as +1.

The waterfall chart should work according to a slicer based on REFERENCE_DATE, so if I select just 30/11/2024 and 31/01/2025 the chart should consider the variations between these 2 reference date.

Any suggestion on how to achieve the final result?

Thanks,

Andrea

[Anonymous · Accepted solution]

Hi @arimoldi 

Based on your description,   you can refer to the following solution.

Sample data is the same as you provided.

1.Create a type table.

2.Create the following measures.

Level =
IF (
    SELECTEDVALUE ( 'Table'[STATUS] ) = "CLOSED",
    0,
    SWITCH ( SELECTEDVALUE ( 'Table'[SEVERITY] ), "HIGH", 3, "MEDIUM", 2, "LOW", 1 )
)

Define =
VAR a =
    MAXX (
        TOPN (
            1,
            FILTER (
                ALLSELECTED ( 'Table' ),
                [REFERENCE_DATE] < MAX ( 'Table'[REFERENCE_DATE] )
                    && [CODE] IN VALUES ( 'Table'[CODE] )
            ),
            [REFERENCE_DATE], DESC
        ),
        [Level]
    )
VAR b = [Level]
RETURN
    IF ( b = a || a = BLANK (), "Keep", IF ( b > a, "Upper", "Lower" ) )

Counts =
VAR a =
    ADDCOLUMNS ( ALLSELECTED ( 'Table' ), "Test", [Define] )
VAR b =
    COUNTROWS (
        FILTER (
            a,
            [Test]
                IN VALUES ( 'Type'[Type] )
                    && [REFERENCE_DATE] IN VALUES ( 'Table'[REFERENCE_DATE] )
        )
    )
RETURN
    IF ( SELECTEDVALUE ( 'Type'[Type] ) = "Lower", 0 - b, b )

Then create a stacked Column visual, and put the following field to it.

Output

Best Regards!

Yolo Zhu

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