Identify date changes for each product ID when status changes

[User57639205]

In my table, i combine the daily status reports for many products, I want to be able to identify only dates the product changes state, but include each change not just the first instance if that makes sense.

Example Data for 1 product only 

Report Date	Status	Product ID
04/09/2024	Red	235258
03/09/2024	Red	235258
02/09/2024	Red	235258
01/09/2024	Red	235258
31/08/2024	Red	235258
30/08/2024	Red	235258
29/08/2024	Red	235258
28/08/2024	Red	235258
27/08/2024	Amber	235258
26/08/2024	Amber	235258
25/08/2024	Amber	235258
24/08/2024	Amber	235258
23/08/2024	Amber	235258
22/08/2024	Amber	235258
21/08/2024	Amber	235258
20/08/2024	Amber	235258
19/08/2024	Amber	235258
18/08/2024	Amber	235258
17/08/2024	Amber	235258
16/08/2024	Red	235258
15/08/2024	Red	235258
14/08/2024	Red	235258
13/08/2024	Green	235258
12/08/2024	Green	235258

[Chewdata · Accepted solution]

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!

[v-xinruzhu-msft · Accepted solution]

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.

[Chewdata · Accepted solution]

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!

[User57639205]

I need to be able to calculate for a months view - how many products changed from amber - red, red- amber etc 

[dharmendars007]

Hello @User57639205 , 

Can you please let us know the expected result with the sample data, this will help us to assist you further.

Thanks

Dharmendar S

[User57639205]

In a ideal world an outcome like this would be great

Product ID	Status	Occurrence	Start Date	End Date
235258	Red	2	28/08/2024	04/09/2024
235258	Amber	1	17/08/2024	27/08/2024
235258	Red	1	14/08/2024	16/08/2024
235258	Green	1	12/08/2024	13/08/2024