Counting number of continuos same entries

[saurav_0101]

Hello All,

I am trying to count number of continuous same entries. Please see below example

		Expected Outcome
Date	Pet	Number of days
1/1/2020	Cat	1
1/2/2020	Cat	2
1/3/2020	Cat	3
1/4/2020	Cat	4
1/5/2020	Dog	1
1/6/2020	Dog	2
1/7/2020	Dog	3
1/8/2020	Rat	1
1/9/2020	Cat	1
1/10/2020	Cat	2
1/11/2020	Rabbit	1
1/12/2020	Rabbit	2
1/13/2020	Rabbit	3
1/14/2020	Rabbit	4
1/15/2020	Rabbit	5
1/16/2020	Rabbit	6
1/17/2020	Cat	1
1/18/2020	Cat	2
1/19/2020	Dog	1
1/20/2020	Dog	2
1/21/2020	Dog	3
1/22/2020	Dog	4
1/23/2020	Rat	1
1/24/2020	Rat	2
1/25/2020	Rat	3
1/26/2020	Rat	4
1/27/2020	Dog	1
1/28/2020	Cat	1
1/29/2020	Cat	2
1/30/2020	Dog	1
1/31/2020	Rabbit	1
2/1/2020	Rabbit	2
2/2/2020	Rabbit	3
2/3/2020	Rabbit	4
2/4/2020	Rabbit	5
2/5/2020	Cat	1
2/6/2020	Cat	2
2/7/2020	Cat	3
2/8/2020	Cat	4
2/9/2020	Dog	1
2/10/2020	Dog	2
2/11/2020	Dog	3
2/12/2020	Rat	1
2/13/2020	Cat	1
2/14/2020	Dog	1

As it can be seen in the third column (Expected outcome), it says number of days. Logic is that the it shows number of days the pet was same.

Like on 3rd Jan, cat pet was there for 3 days, 4th jan it was for 4 days. But on 9th Jan it was for 1 day and 10th Jan it was for 2 days.

Please suggest if any DAX command can help.

Thanks in advance.

[CNENFRNL · Accepted solution]

Hi, @saurav_0101 , Maybe a solution by Power Query is way much easier, I think. You may want to try,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdM7DsIwGAPgu2Su9CfOo+0MJ2CtOtAFMbFwfyEQeVT2+kmJ4trdNhcsGDy8m9zl/nb79CUwRabElCtdX48/FaaZaal0a3etfH3wwkI/ehzPxtAcNSfNWXPRPIvXLcJWzg8vLAiDsMgfD0lYFlaEiX4gckA0FEWOqBqCaVW1wVRrMFUa+gbrq9A32Im6glFEGDWFYYODUVM4DbBFpr8Iw/R+Z/cP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Pet = _t]),
    
    #"Grouped Rows" = Table.Group(Source, {"Pet"}, {{"All", each Table.AddIndexColumn(_,"Consecutive Days",1,1)}}, GroupKind.Local),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Pet"}),
    #"Expanded All" = Table.ExpandTableColumn(#"Removed Columns", "All", {"Date", "Pet", "Consecutive Days"}, {"Date", "Pet", "Consecutive Days"})
in
    #"Expanded All"

[wdx223_Daniel · Accepted solution]

@saurav_0101  try this code, it might help you. you need a date table, and link it ot FactTable, date to date.

Distinct_Continuious_Count :=
VAR _CurrentDate =
    MAX ( Dates[Date] )
VAR _MinDate =
    MAXX (
        FILTER (
            ALL ( Dates[Date] ),
            Dates[Date] < _CurrentDate
                && CALCULATE ( ISEMPTY ( FactTable ) )
        ),
        Dates[Date]
    )
RETURN
    IF (
        COUNTROWS ( FactTable ),
        CALCULATE (
            COUNT ( Dates[Date] ),
            Dates[Date] <= _CurrentDate
                && IF ( _MinDate, Dates[Date] > _MinDate, TRUE () )
        )
    )

[CNENFRNL · Accepted solution]

Hi, @saurav_0101 , Maybe a solution by Power Query is way much easier, I think. You may want to try,

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("bdM7DsIwGAPgu2Su9CfOo+0MJ2CtOtAFMbFwfyEQeVT2+kmJ4trdNhcsGDy8m9zl/nb79CUwRabElCtdX48/FaaZaal0a3etfH3wwkI/ehzPxtAcNSfNWXPRPIvXLcJWzg8vLAiDsMgfD0lYFlaEiX4gckA0FEWOqBqCaVW1wVRrMFUa+gbrq9A32Im6glFEGDWFYYODUVM4DbBFpr8Iw/R+Z/cP", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Pet = _t]),
    
    #"Grouped Rows" = Table.Group(Source, {"Pet"}, {{"All", each Table.AddIndexColumn(_,"Consecutive Days",1,1)}}, GroupKind.Local),
    #"Removed Columns" = Table.RemoveColumns(#"Grouped Rows",{"Pet"}),
    #"Expanded All" = Table.ExpandTableColumn(#"Removed Columns", "All", {"Date", "Pet", "Consecutive Days"}, {"Date", "Pet", "Consecutive Days"})
in
    #"Expanded All"