Forum Discussion

mariner84's avatar
mariner84
Icon for Advocate I rankAdvocate I
7 years ago
Solved

Show when records change?

Hello,

I am wondering if Power BI is able to show when records change for an individual.

In the table below, each person has a new record created when they have a change in their value, or score. I would like to know if its possible to create a matrix to show only when a Score has changed, and then figure out the difference in value changes, % difference of value change, previous score and new score - (see second picture for example of desired result).

An end date of 1/1/9999 would indicate this person is still in that score and value if that means anything.

 

I thought maybe using 'Earlier' formula to get the end date before 1/1/9999 would somewhat work but it ended up not making sense to me in the end.

 

 

 

 

 

 

 

 

 

 

Not sure if this is possible, but anything would be great. The main goal would be to identify when the score changed for a person and the dates, the other things such as value change, and value change (%) are more of a nice to have.

 

Really appreciate any help.

 

Need Help
  • v-lid-msft's avatar
    v-lid-msft
    7 years ago

    Hi mariner84 ,

     

    Based on your shared table, we can create a calculated table using following formula to meet your requirement:

     

    Desired Table = 
VAR t =
    SUMMARIZECOLUMNS (
        'Data'[ID],
        'Data'[Name],
        'Data'[Score],
        "Start Date", MIN ( 'Data'[Start Date] ),
        "End Date", MAX ( 'Data'[End Date] ),
        "Value", MAX ( 'Data'[Value] ),
        "MaxValue", MAX ( 'Data'[Value] ),
        "MinValue", MIN ( 'Data'[Value] )
    )
VAR t2 =
    ADDCOLUMNS (
        FILTER (
            ADDCOLUMNS (
                t,
                "Temp", COUNTROWS ( FILTER ( t, 'Data'[Name] = EARLIER ( Data[Name] ) ) )
            ),
            [Temp] > 1
        ),
        "index", RANKX ( FILTER ( t, 'Data'[ID] = EARLIER ( Data[ID] ) ), [Start Date],, ASC )
    )
VAR t3 =
    ADDCOLUMNS (
        t2,
        "Value Change", MAXX (
            FILTER ( t2, [index] = EARLIER ( [index] ) + 1 && [ID] = EARLIER ( [ID] ) ),
            [MinValue]
        ) - [MaxValue]
    )
VAR t4 =
    ADDCOLUMNS (
        t3,
        "Value Change %", [Value Change] / [Value],
        "Change End Date", [End Date],
        "Change Start Date", MAXX (
            FILTER ( t3, [index] = EARLIER ( [index] ) + 1 && [ID] = EARLIER ( [ID] ) ),
            [Start Date]
        ),
        "Prior Score", [Score],
        "Current Score", MAXX (
            FILTER ( t3, [index] = EARLIER ( [index] ) + 1 && [ID] = EARLIER ( [ID] ) ),
            [Score]
        )
    )
VAR resultt =
    SELECTCOLUMNS (
        FILTER ( t4, [Temp] <> [index] ),
        "ID", [ID],
        "Name", [Name],
        "Value Change", [Value Change],
        "Value Change (%)", [Value Change %],
        "Prior Score Change", [Prior Score],
        "Current Score", [Current Score],
        "Change End Date", [Change End Date],
        "Change Start Date", [Change Start Date]
    )
RETURN
    resultt

     

     

     

    BTW, pbix as attached.

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.