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 i...
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

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