Forum Discussion

mariner84's avatar
mariner84
Icon for Advocate I rankAdvocate I
6 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...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 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.

kentyler's avatar
kentyler
Icon for Solution Sage rankSolution Sage
6 years ago

Once you have your index and can use it to find the preceding row your pattern is going to be something like

 

For any row, look at the row above it. If that row is not for the same person, then return 0. If that row is for the same person and the score is different, then return the difference in the values. 

once you have the difference ("value change") you can do your other calculations.

 

The problem is, although you can do this in DAX, because its has no built in notion of the preceding row, you have to filter the whole data set every time you move to a new row. Much better to do it in Excel, which does have a built in concept of "the preceding row" and then load the dataset into power bi to build your reports.

mariner84's avatar
mariner84
Icon for Advocate I rankAdvocate I
6 years ago

Ah I get it now, thank you. I'll give this a try shortly here.

 

My real dataset is going to be about 10,000 rows. As you mentioned, this would be better solved in Excel and then loaded to Power BI? Would have happen to know what formula this would require? Sorry for the questions, I'm quite new to this world =/