Identify date changes for each product ID when status changes

Hey,

edit: Didn't see the month requirement. See changed code.

This can be done with a GroupBy function in Power Query:

Replace the Source step with your data.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("hdCrDoAwEETRf6luwu72QStReCzBECpB8P+CSkLCjLnmuLuuTuIgdTCx6Lxb2tFrIVkqbvNdA1SDqkhD1/KvgtQq1AJ1fOl07u3+eCaeiEfigbgRV+KCXSvxQpz804zua4Iaob7PzXdr18cN+PYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"Report Date" = _t, Status = _t, #"Product ID" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Report Date", type date}, {"Status", type text}, {"Product ID", Int64.Type}}),
    #"add_Year-Month" = Table.AddColumn(#"Changed Type", "Year-Month", each Text.From(Date.Year([Report Date])) & "-" & Text.From(Date.Month([Report Date]))),
    #"Grouped Rows" = Table.Group(#"add_Year-Month", {"Product ID", "Status", "Year-Month"}, {{"Occurence", each Table.RowCount(_), Int64.Type}, {"Start Date", each List.Min([Report Date]), type nullable date}, {"End Date", each List.Max([Report Date]), type nullable date}})
in
    #"Grouped Rows"

 Hopefully this helps!

Hi User57639205 

You can try the following sample.

Sample data.

1.Create two calculated column in table.

Laststatusdate =
VAR laststatusdate =
    MAXX (
        FILTER (
            'Table',
            [Status] <> EARLIER ( 'Table'[Status] )
                && [Product ID] = EARLIER ( 'Table'[Product ID] )
                && [Report Date] < EARLIER ( 'Table'[Report Date] )
        ),
        [Report Date]
    )
RETURN
    IF (
        laststatusdate = BLANK (),
        MINX (
            FILTER (
                ALLSELECTED ( 'Table' ),
                [Product ID] = EARLIER ( 'Table'[Product ID] )
            ),
            [Report Date]
        ),
        laststatusdate
    )

Index =
RANKX ( 'Table', [Laststatusdate],, ASC, DENSE )

2.Create a new table.

Table 2 = SUMMARIZE('Table',[Index],[Product ID],[Status],[Laststatusdate])

3.Create the following measures.

Mindate =
CALCULATE (
    MIN ( 'Table'[Report Date] ),
    'Table'[Index] IN VALUES ( 'Table 2'[Index] ),
    'Table'[Report Date] >= MAX ( 'Table 2'[Laststatusdate] )
)

MaxDate =
CALCULATE (
    MAX ( 'Table'[Report Date] ),
    'Table'[Index] IN VALUES ( 'Table 2'[Index] ),
    'Table'[Report Date] >= MAX ( 'Table 2'[Laststatusdate] )
)

Occurrence =
COUNTROWS (
    FILTER (
        ALLSELECTED ( 'Table 2' ),
        [Status]
            IN VALUES ( 'Table 2'[Status] )
                && [Product ID]
                    IN VALUES ( 'Table 2'[Product ID] )
                        && [Index] <= MAX ( 'Table 2'[Index] )
    )
)

Then putthe following field to a table visual.

Output

Best Regards!

Yolo Zhu

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