Consecutive Occurrences and Reset If Value

[ojmayo21]

Trying to create a calculated column that counts the consecutive months that a ID appears but resets when the total usage is greater than 0. The dax code works but doesn't reset back to 0 when the next months total usage > 0, it just continues the streak count.

This is the expected output:

| Number | Total Usage | Date     | Streak |
|--------|-------------|----------|--------|
| A      | 0           | 1/1/2023 | 1      |
| A      | 0           | 2/1/2023 | 2      |
| A      | 1           | 3/1/2023 | 0      |
| A      | 0           | 4/1/2023 | 1      |
| A      | 0           | 5/1/2023 | 2      |

Dax:

Streak  = 
VAR CurrID = [Number]
VAR CurrDate = [Date]
VAR PreviousRows =
    FILTER(
        ALL('Table'),
        [Number] = CurrID &&
        [Date] < CurrDate
    )
VAR StreakCount =
    COUNTROWS(
        FILTER(
            ADDCOLUMNS(
                PreviousRows,
                "Usage",
                [Total Usage]
            ),
            [Usage] = 0
        )
    )
RETURN
    IF(
        [Total Usage] = 0,
        1 + StreakCount,
        0
    )

[amitchandak]

@ojmayo21 , refer these approches

Continuous streak: https://youtu.be/GdMcwvdwr0o

Continuous Streak -https://community.powerbi.com/t5/Desktop/Need-help-in-DAX/m-p/1277302#M559393
https://community.powerbi.com/t5/Quick-Measures-Gallery/Power-BI-Continuous-Streak-One-Day-Differenc...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Continuous-Streak-With-One-Day-Break/ba-p/1...
https://community.powerbi.com/t5/Quick-Measures-Gallery/Power-BI-Continuous-Streak-One-Day-Differenc...
http://dataap.org/blog/2018/05/30/solved-microsoft-access-the-microsoft-ace-oledb-12-0-provider-is-n...

[ojmayo21]

@amitchandak

I tried several of the solutions but couldn't get it to work. Consecutive column adds increments when Total Usage is 0 sometimes but then others it wouldn't add up. Some dates range from 4/27/23 - 5/27/23 with 0 Total Usage and consecutive will be 1,1.

| ID Number  | Date       | Total Usage | ResultCount | Latest Consecutive Streak | Months | consecutive |
|------------|------------|-------------|-------------|---------------------------|--------|-------------|
| A| 12/27/2022 | 0           | 1           | 1                         | 1      | 1           |
| A| 1/27/2023  | 200         | 0           | 1                         | 0      | 0           |
| A| 2/27/2023  | 0           | 2           | 1                         | 1      | 1           |
| A| 3/27/2023  | 0           | 3           | 1                         | 1      | 2           |
| A| 4/27/2023  | 0           | 4           | 1                         | 1      | 3           |
| A| 5/27/2023  | 100000      | 0           | 1                         | 0      | 0           |
| A| 6/27/2023  | 0           | 5           | 1                         | 1      | 1           |
| B| 1/27/2023  | 0           | 1           | 1                         | 1      | 1           |
| B| 2/27/2023  | 0           | 2           | 1                         | 1      | 1           |
| B| 3/27/2023  | 0           | 3           | 1                         | 1      | 1           |
| B| 4/27/2023  | 0           | 4           | 1                         | 1      | 1           |
| B| 5/27/2023  | 0           | 5           | 1                         | 1      | 1           |
| B| 6/27/2023  | 0           | 6           | 1                         | 1      | 1           |

consecutive = 
VAR ID = ' Total Usage'[ID Number]
VAR CurrentDate = ' Total Usage'[Date]
VAR PrevDate =
    CALCULATE(
        MAX(' Total Usage'[Date]),
        FILTER(
            ALL(' Total Usage'),
            ' Total Usage'[ID Number] = ID &&
            ' Total Usage'[Date] < CurrentDate &&
            ' Total Usage'[Total Usage] > 0
        )
    )
RETURN
    IF(
        ISBLANK(PrevDate),
        IF(
            ' Total Usage'[Total Usage] = 0,
            1,
            0
        ),
        IF(
            ' Total Usage'[Total Usage] = 0,
            DATEDIFF(PrevDate, CurrentDate, MONTH),
            0
        )
    )