IF zero use last nonzero?

[Riggan2023]

Hi. I need help. I have a date table and a TOTAL measure

Totals = COALESCE( COUNT('Dataset'[ArchDate]), 0)

that counts total occurances for each date. But if there is no occurances so of course it put 0. But I dont need zeroes, I need to replace them by the last non zero values in the same column just like this. I use filters so the only way to keep it dinamical is to use measure

Thi is my sample pbix

https://file.io/fXnVvkxSIpdd 

[Anonymous · Accepted solution]

Hi @Riggan2023 ,

I updated your sample pbix file(see the attachment), please check if that is what you want.

1. Create a measure as below to get the count of per ArchDate

Measure = COUNT('Dataset'[ArchDate])

2. Update the formula of measure [Totals] as below

Totals =
VAR _seldate =
    SELECTEDVALUE ( 'MyCalendar'[Date] )
VAR _predate =
    CALCULATE (
        MAX ( 'Dataset'[ArchDate] ),
        FILTER (
            ALLSELECTED ( 'Dataset' ),
            'Dataset'[ArchDate] < _seldate
                && NOT ( ISBLANK ( [Measure] ) )
        )
    )
VAR _count =
    CALCULATE (
        [Measure],
        FILTER ( ALLSELECTED ( 'Dataset' ), 'Dataset'[ArchDate] = _predate )
    )
RETURN
    IF ( NOT ( ISBLANK ( [Measure] ) ), [Measure], _count )

Best Regards