Compare data/snapshots of data based on log time stamps

[nhoff]

I need help with comparing evolution of the same data across different snapshots from a data source. I am simplifying my example.

Everyday I get a snapshot of invoice data in a csv file. I import the data in the same table tblInvoice:

InvoiceID

InvoiceAmount

InvoiceStatus

LogID

Each dataload get a LogID.

Consequently I have a Logtable with

LogID

LogTimestamp

LogRank = RANK.EQ(Log[LogID],Log[LogID],DESC) --- making sure I always can filter for the latest Log

I have some measures, e.g

TotalAmount:= Calculate(SUM(tblInvoice[InvoiceAmount]))

InvoiceCount = Calculate(Countrows(tblInvoice))

I also have a date table.

I want to be able to compare e.g. the relative InvoiceCount between e.g. LogRank 1 and 2. The business value of this is to understand the development in invoice status across time. Any suggestions?

[Eric_Zhang · Accepted solution]

@nhoff

In my opinion you can create a calculated table to get the rank 1and 2 invoices. Make sure there's a many to one relationship between Invoices and LogTable.

Top2LatestLoadedData = CALCULATETABLE(Invoices,TOPN(2,LogTable,LogTable[LogRank ]))

Then apply your measure against this table.

TotalAmount= Calculate(SUM(Top2LatestLoadedData[InvoiceAmount]))

InvoiceCount = Calculate(Countrows(Top2LatestLoadedData))

[Vvelarde]

hi @nhoff

An idea

Measure =
VAR LOGRANKER=Max(Logs[LogRank])-1
RETURN
[InvoiceCount]-CALCULATE(Invoices[InvoiceCount];FILTER(all(Logs);Logs[LogRank]=LOGRANKER))