Show when records change?

[mariner84]

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.

[v-lid-msft · Accepted solution]

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.

[adelmonte]

Hi,
How could I adapt this script for my needs?

I have a table with Orders, Products, Delivery Date, Email update date, Email update time, Depo1, Depo2
I receive up to 4 updates of initial order for one OrderID, and Delivery Date where quantity can change. I need a column with the latest updated quantity, one column with old quantity per Depo1 and Depo2 the same.

Regards,
Alex

RAW Table

Desired outcome

[viferenc]

Hi, 

I´m using your dax formula for my calculation, in case  I do not get the correct view.

this is the base data from where I take data.

I use the followin formula:

Desired Table =

VAR t =

SUMMARIZECOLUMNS (

'Gross Price'[Material],

'Gross Price'[Customer],

'Gross Price'[Gross price EUR],

"Start Date", MIN ( 'Gross Price'[Valid From] ),

"End Date", MAX ( 'Gross Price'[Valid to ]),

"Value", MAX ( 'Gross Price'[Gross price EUR] ),

"MaxValue", MAX ( 'Gross Price'[Gross price EUR] ),

"MinValue", MIN ( 'Gross Price'[Gross price EUR]))

VAR t2 =

ADDCOLUMNS (

FILTER (

ADDCOLUMNS (

t,

"Temp", COUNTROWS ( FILTER ( t, 'Gross Price'[Customer] = EARLIER('Gross Price'[Customer])))

),

[Temp] > 1

),

"index", RANKX ( FILTER ( t, 'Gross Price'[Material] = EARLIER ('Gross Price'[Material] ) && 'Gross Price'[Customer]=EARLIER('Gross Price'[Customer])), [Start Date],, ASC ))

VAR t3 =

ADDCOLUMNS (

t2,

"Value Change", MAXX (

FILTER ( t2, [index] = EARLIER ( [index] ) + 1 && [Material] = EARLIER ( [Material] )&& [Customer]=EARLIER([Customer]) ),

[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 && [Material] = EARLIER ( [Material] ) && [Customer]=EARLIER([Customer]) ),

[Start Date]

),

"Prior Score", [Gross price EUR],

"Current Score", MAXX (

FILTER ( t3, [index] = EARLIER ( [index] ) + 1 && [Material] = EARLIER ( [Material] ) && [Customer]=EARLIER([Customer])),

[Gross price EUR]

)

)

VAR resultt =

SELECTCOLUMNS (

FILTER ( t4, [Temp] <> [index] ),

"ID", [Material],

"Name", [Customer],

"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

My result:

The problem is, that I should see 3 line because from 01.08.2021 the current price is 8,67€ (there was decrease) and not 8,74€

Thank you for your help

BR

Feri

[mariner84]

@v-lid-msft 

Wow this is great. Really appreciate it. I would have never gotten to this point, so thank you and also to @kentyler 

[kentyler]

Unfortunately "EARLIER" is sort of misnamed, it returns a value from an outer filter function. I found this post which seems to be what  you could use

In DAX, there's no order internally for records in table. You have to add an index column in your table. 

PreviousText = CALCULATE(MAX(Table1[Text]),FILTER(Table1,Table1[Index]=EARLIER(Table1[Index])-1))

[mariner84]

Thank you @kentyler for responding so quickly and explaining "EARLIER".

I'm going to give this a shot when I get home, but just reading, would I create an index table and then assign that number to each person? (ie. "A" is assigned to all of John Smith?)

[kentyler]

The index is to order the table so you can look for min and max values. In your example you can't use the ID, because it repeats

You need a sequential index  1,2,3,4  applied to all the rows so that when you're on a given row you can use < to find the row above it. This assumes you have the rows sorted to make that the one you want.

[kentyler]

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]

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 =/

[kentyler]

send me a sample chunk of data as an excel file and I'll try and write it for you.

[mariner84]

@kentyler 

Really appreciate your help and time!

Here is a link: https://www.dropbox.com/s/pidsub8oj08f3kd/Book1.xlsx?dl=0

Thank you