Retrieving the last non-zero value from the column (with filters)

[Rafgva]

Good day everyone!

Here is a problem:

We have very simple table with reported values. No dates, only quarter number and a year, as a separate columns.

If test was not done, column SCORE contains value ZERO (0).

I need to present last non-zero value when quarter slicer is used. That means, if Q2 is selected, I would like to have a measure that returns values for Q2, but in case this value is 0 it will look backward and return non-zero value from Q1.

In case Q1 value is zero, it should return 0.

Similarily, if Q1 is non-zero, but Q2 and Q3 are zeroed, selecting Q3 should return value from latest non-zero quarter, hence Q1.

PowerBI report and screenshot from Power BI are available here:

https://1drv.ms/u/s!As7K3fOaTOoxg6BiXBulVsHyQ6sSWQ?e=tv51Og

For this particular dataset, I wish, after selecting Q2, to see Q1 score for T5 and Q2 scores for T1, T2 and T3 - not null or zero, as I can achieve today.

Similarly for T2, selecting Q3 should retun 60, selecting Q4 should also return 60, not 0 (neither 70 which is MAX).

I hope this is described with sufficent clarity. Be safe and thank you in advance for your ideas and hints.

Ah, almost forgotten, I am looking for measure. If there is a need to modify source data column, that would be OK, but final calculation must be dynamic. Thanks in advance!

RAF

[mahoneypat · Accepted solution]

Please use this measure expression.  Note that I changed your Card[Quarter] data type to Whole Number.  It also works as text but it should be changed since numeric comparisons are being made.

Last NonZero =
VAR _selquarter =
    MAX ( Card[Quarter] )
VAR _summary =
    CALCULATETABLE (
        SUMMARIZE (
            Card,
            Card[Name],
            Card[Quarter],
            Card[Score]
        ),
        ALL ( Card[Quarter] ),
        Card[Quarter] <= _selquarter
    )
VAR _top1nonzero =
    TOPN (
        1,
        FILTER (
            _summary,
            Card[Score] > 0
        ),
        Card[Quarter], DESC
    )
RETURN
    SUMX (
        _top1nonzero,
        Card[Score]
    )

Regards,

Pat

[Rafgva]

Thank you for solution and inspiration.