Update database column content

[daniel_baciu]

Hello,

I am trying to get a solution for the following topic. I have a rolling database and want to highlight changes from a period to another period. Sample table below (2 heads and 3 periods for simplicity):

Every period there is a new database which is automatically uploaded from Sharepoint in a specific date in a month and appended to the existing database in PBI. 

One head is changing the Allocation and the Reason for Change in P03. I need to highlight this change when comparing P03 with P02 and P03 with P01 (user is chosing which periods they want to compare).

Below is the sample of relations i have. I created 2 referenced tables from Database: one for T0 one for T1. Nothing else much to say.

Below is the summary of changes i managed to put together (2 slicers one from T0 database the other from T1 database) and i created a measure ("Changes") with a simple mathematical substract calculation (Allocation from T1 - Allocation from T0):

All works fine except one most important thing: the Reason for Change. As we see, PBI sees that there is -1 in P03 vs P02, but the reason of change that he recognises is "Addition", while i want it to be "Left Company".

I understood why this happen (below is the obvious logic split by period), as it takes the reason for change for the same item "Addition" which has the values; since "left company" was 0 in all periods, the result is also zero...

Now... the solution i think is needed in this case is to show the most updated reason for change that gets updated only with the P03 database (and ideally to consider the reason for change from the period selected in T1 slicer...). When they take out the head, they also update his reason for change, so in P03 they made him 0 and updated his reason for change to "Left Company". You could argue that showing a -1 in "Addition", it is obvious that is a reduction, and yes, it is, but there are multiple reasons for decreasing a head such as "left company", "transfer". "correction", "restructuring" etc. and i would like to highlight them for a more meaningful data...

Does anyone have a solution for this?

Pbix is saved here: https://drive.google.com/file/d/1FmB6yAujOc6SBkAKwgM2VCYgZeHbRC3_/view?usp=drive_link

Thanks!

[lbendlin]

Power BI has no memory. You need to record these data points further upstream.