Using an Audit Table to show the state of things at an arbitrary point in time? I have no idea.

[mmace1]

Hi,

At my company, we have customers, who in turn have vendors who accept various forms of payment.  Summarizing say, right now, how many of a customer's vendors accept <X> form of payment, is easy.

Here's a look at a modified/truncated version of the table we'd use for that.  The Payment Type column has the data we're looking for.

VendorListID	CustomerNumber	VendorId	ForeignId	Name	AddressLine1	MiD	Payment Type
3055021	665-004	EDE60B3C-7E89-4	COG275	Example name 1	Address 1	DE15CFC4-7827-4	1212
3055020	665-004	29146617-E12B-4	CEN036	Example name 2	Address 2	2B77CA02-1999-4	1212
3055019	665-004	E7A8D372-805F-4	CBS100	Example name 3	Address 3	F241B631-5BCD-4	1212
3055018	665-004	D6D4D794-5E70-4	BER100	Example name 4	Address 4	9E0E7251-C0BE-4	1212
3055017	665-004	ABFED05D-286C-4	ADV038	Example name 5	Address 5	8E6CDFCA-2FA7-4	1212
3055016	928-007	37E90AE7-56D0-4	628703	Example name 6	Address 6	C1B761E7-79F4-4	1212
3055015	928-007	4E6C6E20-21BB-4	215823	Example name 7	Address 7	8798A7B8-5752-4	1212
3055014	928-007	352F97B5-242A-4	15660	Example name 8	Address 8	059B2F66-9B94-4	1212
3055013	928-007	A6D2E12A-4DA5-4	15563	Example name 9	Address 9	F6DCCD9A-7410-4	1212
3055012	928-007	EBFB1E08-CA41-4	15506	Example name 10	Address 10	73C07C79-F258-4	1212

But then, what if we wanted to see say - a week ago, what was the status of all of our customer's suppliers?   How has it changed since last week?  Since 2 weeks ago?   Well, we have an audit table that adds an entry every time payment status changes:

VendorListPaymentTypeChangeAuditID	VendorId	MasterVendorID	PaymentType	CreateDateUTC
5975259	1472C3D5-8A7F-4	C216849C-081D-4	612	9/13/2017 1:46
5975257	1472C3D5-8A7F-4	C216849C-081D-4	612	9/13/2017 1:44
5971111	1472C3D5-8A7F-4	C216849C-081D-4	612	9/12/2017 20:23
5971109	1472C3D5-8A7F-4	C216849C-081D-4	612	9/12/2017 20:23
5937853	1472C3D5-8A7F-4	C216849C-081D-4	1060	9/11/2017 14:26
5918780	1472C3D5-8A7F-4	C216849C-081D-4	1060	9/7/2017 13:26
5918772	1472C3D5-8A7F-4	C216849C-081D-4	1060	9/7/2017 13:23
5843005	1472C3D5-8A7F-4	C216849C-081D-4	1060	8/29/2017 14:12
4760484	1472C3D5-8A7F-4	C216849C-081D-4	1060	3/24/2017 17:31
4178081	1472C3D5-8A7F-4	C216849C-081D-4	612	12/7/2016 21:11

You can connect the two via the VendorID (amongst other things). A manual check is easy enough. 

But, how to automate this?  The CustomerTtable - AuditTtable is a one-to-many relationship...not to mention, we're not looking to pull a single piece of data over from the audit table, but rather check...was the entry for vendor <X> any different 1 week ago, 2 weeks ago?  etc.

I fundamentally don't understand how to do this, but to conserve space, I'm guessing audit tables arranged like the above, are typical.  Can anyone point me in a direction to look?

Thanks!

[v-ljerr-msft · Accepted solution]

Hi @mmace1,

If I understand you correctly, you should be able to use the formula below to create a new calculate column in CustomerT table to get the Payment Type last week for Vendor<X>. 

Payment Type Last Week = 
VAR currentVendorId = CustomerT[VendorId]
VAR currentDate =
    CALCULATE (
        MAX ( AuditT[CreateDateUTC] ),
        FILTER ( AuditT, AuditT[VendorId] = currentVendorId )
    )
VAR lastCreateDate =
    CALCULATE (
        MAX ( AuditT[CreateDateUTC] ),
        FILTER (
            AuditT,
            AuditT[CreateDateUTC]
                <= currentDate - 6
                && AuditT[VendorId] = currentVendorId
        )
    )
RETURN
    CALCULATE (
        MAX ( AuditT[PaymentType] ),
        FILTER (
            AuditT,
            AuditT[CreateDateUTC] = lastCreateDate
                && AuditT[VendorId] = currentVendorId
        )
    )

And the Payment Type two weeks ago.

Payment Type Two Weeks ago = 
VAR currentVendorId = CustomerT[VendorId]
VAR currentDate =
    CALCULATE (
        MAX ( AuditT[CreateDateUTC] ),
        FILTER ( AuditT, AuditT[VendorId] = currentVendorId )
    )
VAR lastCreateDate =
    CALCULATE (
        MAX ( AuditT[CreateDateUTC] ),
        FILTER (
            AuditT,
            AuditT[CreateDateUTC]
                <= currentDate - 13
                && AuditT[VendorId] = currentVendorId
        )
    )
RETURN
    CALCULATE (
        MAX ( AuditT[PaymentType] ),
        FILTER (
            AuditT,
            AuditT[CreateDateUTC] = lastCreateDate
                && AuditT[VendorId] = currentVendorId
        )
    )

Regards

[mmace1]

Wow...that's not that long of a formula, yet I don't understand it yet.  It seems to work though, thanks!