How to hide cumulative bars if null/blank

[Anonymous]

Hi all,

I'm currently working on this chart, and as Q3 and Q4 data are not in yet, is there a way I can hide the Q3 and Q4 dark blue bars?

This is the simplified dataset:

Date	Q1	Q2	Q3	Q4
realized	80	20	NULL	NULL
forecasted	68	92	73	74

I had used the 'running total' quick measure to get the cumulative totals, and this was the resulting formula for 'realized' chart:

CALCULATE(

    SUM(Realized),

    FILTER(

        CALCULATETABLE(

            SUMMARIZE('Calendar', 'Calendar'[CurrentDayOffset], 'Calendar'[Date]),

            ALLSELECTED('Calendar')

        ),

        ISONORAFTER(

            'Calendar'[CurrentDayOffset], MAX('Calendar'[CurrentDayOffset]), DESC,

            'Calendar'[Date], MAX('Calendar'[Date]), DESC

        )

    )

)

Any help would be appreciated, thank you!
 

[Kishore_KVN · Accepted solution]

Hello @Anonymous ,

Please ignore blanks during the calculation. Your measure should look as below:

If(IsBlank(Realized),Blank(),

CALCULATE(

    SUM(Realized),

    FILTER(

        CALCULATETABLE(

            SUMMARIZE('Calendar', 'Calendar'[CurrentDayOffset], 'Calendar'[Date]),

            ALLSELECTED('Calendar')

        ),

        ISONORAFTER(

            'Calendar'[CurrentDayOffset], MAX('Calendar'[CurrentDayOffset]), DESC,

            'Calendar'[Date], MAX('Calendar'[Date]), DESC

        )

    )
)

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