Forum Discussion

Riggan2023's avatar
Riggan2023
Frequent Visitor
3 years ago
Solved

IF zero use last nonzero?

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 

 

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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