Need to capture state with min change date for every state as shown from table1 to table 2

[Pravdba]

Hi, 

Help me on this, I have table1 contains historical data i need to achieve Table 2,  Need to capture min change date of every state if it was repeated state after state it should also capture.

Table 1

ID	State	ChangeDate
1	Proposed	08-03-2024 00:00
1	Proposed	09-03-2024 00:00
1	Analysis	25-03-2025 00:00
1	Authoring	26-03-2025 00:00
1	Authoring	27-03-2025 00:00
1	Blocked	06-02-2025 00:00
1	Blocked	07-02-2025 00:00
1	Analysis	08-02-2025 00:00
2	Proposed	08-03-2024 00:00
2	Authoring	25-03-2025 00:00
2	Analysis	29-07-2024 00:00
2	Blocked	06-02-2025 00:00
2	Blocked	07-02-2025 00:00
2	Authoring	08-02-2025 00:00

Table 2

Id	State	MinChangeDate
1	Proposed	08-03-2024 00:00
1	Analysis	25-03-2025 00:00
1	Authoring	26-03-2025 00:00
1	Blocked	06-02-2025 00:00
1	Analysis	08-02-2025 00:00
2	Proposed	08-03-2024 00:00
2	Authoring	25-03-2025 00:00
2	Analysis	29-07-2024 00:00
2	Blocked	06-02-2025 00:00
2	Authoring	08-02-2025 00:00

Thanks

Praveen Kumar

[BeaBF · Accepted solution]

@Pravdba Try wih this measure:

Table2 =
VAR AddIndex =
ADDCOLUMNS (
Table1,
"PrevState",
VAR PrevRow =
CALCULATE (
MAX ( Table1[State] ),
Table1[ID] = EARLIER ( Table1[ID] ),
Table1[ChangeDate] < EARLIER ( Table1[ChangeDate] )
)
RETURN IF ( PrevRow <> Table1[State], Table1[State], BLANK () )
)
VAR FilteredTable =
FILTER ( AddIndex, NOT ISBLANK ( [PrevState] ) )
RETURN
SUMMARIZE ( FilteredTable, Table1[ID], Table1[State], "MinChangeDate", MIN ( Table1[ChangeDate] ) )

BBF

[Pravdba]

Hi, 

Help me on this, I have table1 contains historical data i need to achieve Table 2,  Need to capture min change date of every state if it was repeated state after state it should also capture.

Table 1

ID	State	ChangeDate
1	Proposed	08-03-2024 00:00
1	Proposed	09-03-2024 00:00
1	Analysis	25-03-2025 00:00
1	Authoring	26-03-2025 00:00
1	Authoring	27-03-2025 00:00
1	Blocked	06-02-2025 00:00
1	Blocked	07-02-2025 00:00
1	Analysis	08-02-2025 00:00
2	Proposed	08-03-2024 00:00
2	Authoring	25-03-2025 00:00
2	Analysis	29-07-2024 00:00
2	Blocked	06-02-2025 00:00
2	Blocked	07-02-2025 00:00
2	Authoring	08-02-2025 00:00

Table 2

Id	State	MinChangeDate
1	Proposed	08-03-2024 00:00
1	Analysis	25-03-2025 00:00
1	Authoring	26-03-2025 00:00
1	Blocked	06-02-2025 00:00
1	Analysis	08-02-2025 00:00
2	Proposed	08-03-2024 00:00
2	Authoring	25-03-2025 00:00
2	Analysis	29-07-2024 00:00
2	Blocked	06-02-2025 00:00
2	Authoring	08-02-2025 00:00

Thanks

Praveen Kumar

[v-achippa]

Hi @Pravdba,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @lbendlin for the prompt response.

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the super user's resolved your issue? or let us know if you need any further assistance.
If our super user's response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @Pravdba,

We wanted to kindly follow up to check if the solution provided by the super user's resolved your issue.
If our super user's response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @Pravdba,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the super user's resolved your issue.
If our super user's response resolved your issue, please mark it as "Accept as solution" and click "Yes" if you found it helpful.

Thanks and regards,

Anjan Kumar Chippa

[lbendlin]

Read about GroupKind.Local

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMlTSUQooyi/IL05NATIt9I31jQyMTBQMrAwMlGJ1MBRYYlXgmJeYU1mcWQxkGpnqGhjrApWYKhigqiktycgvysxLBykyI0aROQ5FTjn5ydlg15jpG+lDFGCXN8cqj+RaCwwFRoQCxAjdmdg8bIQWKJa6Bua6EHNQ1ODzihEBr6A7BM0vsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [ID = _t, State = _t, ChangeDate = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"ChangeDate", type datetime}},"en-GB"),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"ID", "State"}, {{"Min Date", each List.Min([ChangeDate]), type nullable datetime}},GroupKind.Local)
in
    #"Grouped Rows"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". Once you examined the code, replace the entire Source step with your own source.

[v-priyankata · Accepted solution]

Hi @Pravdba ,

Thank you for reaching out to the Microsoft Community Forum.

DAX code to capture min change date of every state if it was repeated state.

Table2 = 
ADDCOLUMNS (
    SUMMARIZE (
        Table1,
        Table1[ID],
        Table1[State],
        "MinChangeDate", MIN ( Table1[ChangeDate] )
    ),
    "MinChangeDate", 
    VAR CurrentState = EARLIER ( Table1[State] )
    VAR CurrentID = EARLIER ( Table1[ID] )
    RETURN
        CALCULATE (
            MIN ( Table1[ChangeDate] ),
            FILTER (
                Table1,
                Table1[State] = CurrentState
&& Table1[ID] = CurrentID
            )
        )
)

If my response has resolved your query, please mark it as the Accepted Solution to assist others. Additionally, a 'Kudos' would be appreciated if you found my response helpful.

Thank you

[v-dineshya]

Hi @Pravdba ,

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

[v-dineshya]

Hi @Pravdba ,

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

[v-dineshya]

Hi @Pravdba ,

If you find this post helpful, please mark it as an "Accept as Solution" and consider giving a KUDOS. Feel free to reach out if you need further assistance.
Thanks and Regards

[BeaBF · Accepted solution]

@Pravdba Try wih this measure:

Table2 =
VAR AddIndex =
ADDCOLUMNS (
Table1,
"PrevState",
VAR PrevRow =
CALCULATE (
MAX ( Table1[State] ),
Table1[ID] = EARLIER ( Table1[ID] ),
Table1[ChangeDate] < EARLIER ( Table1[ChangeDate] )
)
RETURN IF ( PrevRow <> Table1[State], Table1[State], BLANK () )
)
VAR FilteredTable =
FILTER ( AddIndex, NOT ISBLANK ( [PrevState] ) )
RETURN
SUMMARIZE ( FilteredTable, Table1[ID], Table1[State], "MinChangeDate", MIN ( Table1[ChangeDate] ) )

BBF