DAX Count consecutive

[topazz11]

Hi,

How do I write DAX to calculate these?

I'd like to achieve the Total column number.

Thank you,

[Ashish_Mathur · Accepted solution]

Hi,

PBI file attached.

Hope this helps.

[Irwan]

hello @topazz11 

please check if this accomodate your need.

1. Unpivot your table and change null value into 0

2. create a calculated column to seek min value of consecutive date

Min Date =
var _Date = 
ADDCOLUMNS(
    ADDCOLUMNS(
        'Table',
        "Next Date", 
        MAXX(
            FILTER(
                'Table',
                'Table'[Item]=EARLIER('Table'[Item])&&
                'Table'[Date]<EARLIER('Table'[Date])
            ),
            'Table'[Date]
        )
    ),
    "ConsDate",
    MAXX(
        FILTER(
            'Table',
            'Table'[Item]=EARLIER('Table'[Item])&&
            'Table'[Date]=[Next Date]
        ),
        'Table'[Value]
    )
)
Return
MAXX(
    FILTER(
        _Date,
        'Table'[Item]=EARLIER('Table'[Item])&&
        'Table'[Value]=1&&
        [ConsDate]=0
    ),
    'Table'[Date]
)

3. create a calculated column to seek max date of consecutive date (this has more syntax since you want the max value even it is not consecutive date for example in Item B at 10-Jan)

Max Date =
var _Date = 
ADDCOLUMNS(
    ADDCOLUMNS(
        ADDCOLUMNS(
            ADDCOLUMNS(
                'Table',
                "Next Date", 
                MAXX(
                    FILTER(
                        'Table',
                        'Table'[Item]=EARLIER('Table'[Item])&&
                        'Table'[Date]<EARLIER('Table'[Date])
                    ),
                    'Table'[Date]
                )
            ),
            "ConsDate",
            MAXX(
                FILTER(
                    'Table',
                    'Table'[Item]=EARLIER('Table'[Item])&&
                    'Table'[Date]=[Next Date]
                ),
                'Table'[Value]
            )
        ),
        "IFMax",
        var _Max = 
        MAXX(
            FILTER(
                'Table',
                'Table'[Item]=EARLIER('Table'[Item])&&
                'Table'[Value]=1&&
                [ConsDate]=1
            ),
            'Table'[Date]
        )
        Return
        IF(
            'Table'[Min Date]='Table'[Date],
            'Table'[Date],
            _Max
        )),
    "MaxDate",
    MAXX(
        FILTER(
            'Table',
            'Table'[Item]=EARLIER('Table'[Item])
        ),
        [IFMax]
    )
)
Return
MAXX(
    FILTER(
        _Date,
        'Table'[Item]=EARLIER('Table'[Item])
    ),
    [MaxDate]
)

4. create a calculated column to calculate datediff between min and max date.

Datediff =
DATEDIFF(
    'Table'[Min Date],
    'Table'[Max Date],
    DAY
)+1

this is quite messy but hopefully you can get the idea where to start.

Hope this will help.

Thank you.

[GeraldGEmerick]

@topazz11 I found a number of references to "Cthulhu" or "Streaks" by Greg Deckler. From what I see the technique should allow you to achieve your desired result. I have not personally tested the DAX code however.

How to find and report on streaks in data | Greg Deckler posted on the topic | LinkedIn