Audit report of changes

macfuller

Active Member
Joined
Apr 30, 2014
Messages
317
Office Version
  1. 365
Platform
  1. Windows
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.
 

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK
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:
Upvote 0
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.
 
Upvote 0
Solution
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.
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,947
Members
448,534
Latest member
benefuexx

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top