Forum Discussion
Helper ITrack date value changes and store new values in table
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
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.
5 Replies
- amitchandak
Super User
JeroenHD , do want a write back, what is the logic for blue column
For write-back you need power app
JeroenHDHello 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/
v-xiaotangCommunity Support
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.
