Audit report of changes

macfuller

Active Member
Joined
Apr 30, 2014
Messages
257
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.
 

Some videos you may like

Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type

macfuller

Active Member
Joined
Apr 30, 2014
Messages
257
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.
 
Last edited:

Redoute

New Member
Joined
Nov 4, 2018
Messages
12
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
Code:
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

Active Member
Joined
Apr 30, 2014
Messages
257
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,089,955
Messages
5,411,486
Members
403,374
Latest member
PMMHart

This Week's Hot Topics

Top