Running Total that Resets when measured value blank

[jcromie]

I am trying to to create a running total of "Days worked" that resets to 0 when a day has a blank value. 

My Formula is this - however it does not reset the count with blank values. 

RollingCount =

CALCULATE(

[# Days],

    FILTER(

        ALLSELECTED(Dim_Date[Date]),

ISONORAFTER(Dim_Date[Date],MAX(Dim_Date[Date]),DESC)))

Current formula is creating below examples:

Dim_Date	Jan 1	Jan 2	Jan 3	Jan 4	Jan5	Jan6	Jan7	Jan8	Jan9
# Days	1	1	0	1	1	1	0	1	1
Rolling Count	1	2	2	3	4	5	5	6	7

What I need: 

	Jan 1	Jan 2	Jan 3	Jan 4	Jan5	Jan6	Jan7	Jan8	Jan9
#Days	1	1	0	1	1	1	0	1	1
Rolling Count	1	2	0	1	2	3	0	1	2

Thanks for the help! 

[v-alq-msft]

Hi, @jcromie 

If you take the answer of someone, please mark it as the solution to help the other members who have same problems find it more quickly. If not, let me know and I'll try to help you further. Thanks.

Best Regards

Allan

[v-alq-msft · Accepted solution]

Hi, @jcromie 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Calendar(a calculated table):

Calendar = CALENDARAUTO()

There is no relationship between two tables. You may create measures as below.

# Days = 
var tab = 
ADDCOLUMNS(
    'Calendar',
    "Result",
    var _result = 
    COUNTROWS(
        FILTER(
            ALL('Table'),
            'Table'[Date]=EARLIER('Calendar'[Dim_Date])
        )
    )
    return
    IF(
        ISBLANK(_result),
        0,
        _result
    )
)
return
SUMX(
    tab,
    [Result]
)

Rolling Count = 
var tab = 
ADDCOLUMNS(
    'Calendar',
    "Result",
    var _result = 
    COUNTROWS(
        FILTER(
            ALL('Table'),
            'Table'[Date]=EARLIER('Calendar'[Dim_Date])
        )
    )
    return
    IF(
        ISBLANK(_result),
        0,
        _result
    )
)
var newtab = 
ADDCOLUMNS(
    tab,
    "Result2",
    var _date = [Dim_Date]
    var tab2 = 
        ADDCOLUMNS(
            ALL('Calendar'),
            "Result",
            var _result = 
            COUNTROWS(
                FILTER(
                    ALL('Table'),
                    'Table'[Date]=EARLIER('Calendar'[Dim_Date])
                )
            )
            return
            IF(
                ISBLANK(_result),
                0,
                _result
            )
        )
    var _lastzerodate = 
    CALCULATE(
        MAX('Calendar'[Dim_Date]),
        FILTER(
            tab2,
            [# Days]=0&&
            [Dim_Date]<_date
        )
    )
    return
    IF(
        [Result]=0,
        0,
        IF(
            ISBLANK(_lastzerodate),
            SUMX(
                FILTER(
                tab2,
                [Dim_Date]<=_date
                ),
                [Result]
            ),
            SUMX(
                FILTER(
                    tab2,
                    [Dim_Date]>=_lastzerodate&&
                    [Dim_Date]<=_date
                ),
                [Result]
            )
        )
    )
)
return
SUMX(
    newtab,
    [Result2]
)

Result:

Best Regards

Allan

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

[Greg_Deckler]

@jcromie - Seems like you want Cthulhu. https://community.powerbi.com/t5/Quick-Measures-Gallery/Cthulhu/m-p/509739#M211