Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Audit report of changes

  1. #1
    Board Regular
    Join Date
    Apr 2014
    Location
    Mitten State
    Posts
    216
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Audit report of changes


    Audit report of changes
    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.

  2. #2
    Board Regular
    Join Date
    Apr 2014
    Location
    Mitten State
    Posts
    216
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Audit report of changes

    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 by macfuller; Apr 13th, 2019 at 09:58 AM.

  3. #3
    New Member
    Join Date
    Nov 2018
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Audit report of changes

    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.

  4. #4
    Board Regular
    Join Date
    Apr 2014
    Location
    Mitten State
    Posts
    216
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Audit report of changes

    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.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •