When a value in a column changes to 0 i need the date it was changed to timestamp in another column?

[Anonymous]

Hi All,

I have created a delivery schedule tab and need to have a column within my table visulisation named "Date Changed"

My data is sourced from SAP and sometimes the delivery date from the vendor is not when the item actually gets delivered. To work this out there is another column named "Still to be delivered" which is either a value of 1 (still to be delivered) or 0 (been delivered).

When the "Still to be delivered" column changes to 0, i need it to display the date this occured in another column when i refresh the data set.

For example if Item 1 changes to 0 in the "still to be delivered" column on the 30/08/23, i need this date to be time stamped in the new "date changed" column. I need this date to be static so when i refresh the data the next day it still displays 30/08/23 not 31/08/23

Any help on this query? 

[danextian]

Hi @Anonymous 

I used to work for a company that used SAP and, for the most part of it, an audit trail was kept within the ERP system itself. If you're connecting directly to SAP, isn't there such a table available when you connect to SAP? If  there isn't, you might want to speak with the developer to have that added. Otherwise, you might have to do it the manual way.

[cassidy]

I don't know that you can pull off the storing of the original Date in BI.

The simplest/manual way would be to record your original Date in an Excel Worksheet as a data source for your BI report, so that when you refresh, while your SAP Date field may update, your Excel Date field will act as the static original. 

You could also probably do it with a SQL procedure that adds to a Table as an automated solution.

[Anonymous]

Yeah i'm really trying to avoid the manual process and try to find a solution that creates a new column within a DAX query. I'm sure there is a way out there! 

[cassidy]

I don't see how that would work as your Source data is being changed, the historical record is lost.  I don't know of a way for DAX to Write data to a table which is what you need.

I agree with @danextian that the Source should be where the static column is added.  Or create your own static Source as a SQL table