Track date value changes and store new values in table

[JeroenHD]

Hello Team, 

I have a table with a purchase order number, line number and a promised date field. I simplified the table. The original table is refreshed overnight.

This Promised Date is what a supplier provides to inform us when we are about to receive the products.
Over time this date is subject to changes. For example a supplier tells us that goods come in a little later on this new date.

The data comes from SQL database, so i want Power BI to write a changed value in the promised date column to a new column. 

We would like to perform analysis on these changes. Not sure if what i'm after is at all possible. 

Is this at all possible and does anybody have an idea of how i should pull this off? 
Any direction would be much appreciated!

PURC_ORDER_ID	LINE_NO	PROMISED DATE	CHANGED DATE 1	CHANGED DATE 2	CHANGED DATE 3	…
00100126	1	2021-05-28	14-6-2021
00100126	2	2021-05-28	14-6-2021
0031092	1	2021-06-14
100001	1	2021-07-05
100001	2	2021-07-05
100161	1	2021-02-10
100161	2	2021-02-10
100183	1	2021-02-10	12-02-2021	14-03-2021
100183	2	2021-02-10	12-02-2021	14-03-2021
100183	3	2021-02-10	12-02-2021	14-03-2021
201301	1	2021-07-20
202275	1	2021-10-29
202275	2	2021-10-29
202830	1	2021-10-29
203785	1	2021-09-07
203786	1	2021-11-29
203788	5	2021-11-29
203788	6	2021-11-29
203788	7	2021-11-29
203788	8	2021-11-29
204378	1	2021-09-20	24-09-2021	20-10-2021	23-11-2021
204632	1	2021-09-07
204633	1	2021-09-07
204955	1	2021-01-05

Kind regards,
Jerone

[v-xiaotang · Accepted solution]

Hi @JeroenHD 

Power BI is used to analyze data, not create data, it means if data changed in your Data Source, then data in Power BI will also change accordingly after you click on refresh button. So if you want to analyze the difference between them, you need to create a new column or new table in your Data Source.

After you add changes to your datasource, then you can create columns bellow,

CHANGED DATE 1 = 
var _predate=RANKX(FILTER(ALL('Table'),'Table'[LINE_NO]=EARLIER('Table'[LINE_NO]) && 'Table'[PURC_ORDER_ID] = EARLIER('Table'[PURC_ORDER_ID])),'Table'[PROMISED DATE],,ASC)
return IF(_predate=2,'Table'[PROMISED DATE])

CHANGED DATE 2 = 
var _predate=RANKX(FILTER(ALL('Table'),'Table'[LINE_NO]=EARLIER('Table'[LINE_NO]) && 'Table'[PURC_ORDER_ID] = EARLIER('Table'[PURC_ORDER_ID])),'Table'[PROMISED DATE],,ASC)
return IF(_predate=3,'Table'[PROMISED DATE])

result

If you need measures, 

measure CHANGED DATE 1 = 
var _predate=RANKX(FILTER(ALL('Table'),'Table'[LINE_NO]=MIN('Table'[LINE_NO]) && 'Table'[PURC_ORDER_ID] = MIN('Table'[PURC_ORDER_ID])),CALCULATE(MIN('Table'[PROMISED DATE])),,ASC)
return IF(_predate=2,MIN('Table'[PROMISED DATE]))

measure CHANGED DATE 2 = 
var _predate=RANKX(FILTER(ALL('Table'),'Table'[LINE_NO]=MIN('Table'[LINE_NO]) && 'Table'[PURC_ORDER_ID] = MIN('Table'[PURC_ORDER_ID])),CALCULATE(MIN('Table'[PROMISED DATE])),,ASC)
return IF(_predate=3,MIN('Table'[PROMISED DATE]))

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@JeroenHD , do want a write back, what is the logic for blue column

For write-back you need power app

https://www.youtube.com/watch?v=LxuRzj0X348

[JeroenHD]

Hello Amitchandak, 

Should have explained it better. Which is an art itself it seems:-). 

The data comes from an SQL database. I would like to know that if a date on a row is changed what the new date is compared to the previous entry. 

Does that make any sense?

Kind regards, 
Jeroen

[amitchandak]

@JeroenHD , there two blog, see if that can help.

https://medium.com/@sam.mckay/show-changes-over-time-in-power-bi-reports-b3e621316cff

https://exceed.hr/blog/how-to-track-changes-in-the-table-in-power-query/

[JeroenHD]

Thanks @amitchandak , will check them out today and let you know.