Audit report of changes

[macfuller]

I would like to create an audit report showing dates when values were changed and what those values were changed to, but only one row for each date the value changed.

We modify payment terms for our vendors and the payment terms show up on the purchase orders (PO). So if we change payment terms from Net 30 to Net 45 on the vendor file on March 1, all POs from March 1 onward will have Net 45 terms. If we then change terms to Net 30 on May 15 all subsequent POs would have Net 30. My audit report would run against the PO table and then show

Vendor - Date of Change - New Terms
American Cleaners - Dec 12 - Net 30
American Cleaners - March 1 - Net 45
American Cleaners - May 15 - Net 30
...

I'm trying various combinations of FIRSTDATE and VALUES but I'm not getting it right as I just get the first value if the terms revert back to a previous setting. Thanks for any help you can give.

 

[macfuller]

BTW, I'm sure someone will wonder why I can't check the audit record in the vendor file where the changes were made... We don't have audit tracking turned on for that table. There are a ton of changes to vendors daily for address, contact info, billing and other info and it was deemed (properly IMO) too "expensive" for computing and storage since we have to pump several thousand lines a day through the system and we can't slow down the core OLTP functionality.

 

[Redoute]

Hm, I tried something similar in Power Pivot, may be you can use this as input.

I took the DimProduct table from Contoso and searched which product keys start a new series of product subcategory. That is a calculated boolean column

newsubpr=DimProduct[ProductSubcategoryKey] <>
    lookupvalue(
        DimProduct[ProductSubcategoryKey];
        DimProduct[ProductKey];
        DimProduct[ProductKey] - 1)

I use this column as filter for a pivot table, and show ProductKey and SubProductKey as row fields, no measures. This requires a continuously numbered primary key, so you would have to create this as a second calculated column (e. g. RANKX() ordered by vendor & date & po_id).

Disclaimer: I'm a beginner with Power Pivot/DAX.

 

[macfuller]

Thanks for your tip. I had considered a calculated column but was worried about the performance hit. I was hoping for a pure measure-based solution but I don't know if it's possible.