Delta calculation in PBI and counter

[michaeldurieux]

Hi everyone,

I have a device with one button communicating by Sigfox.
Each 15 minutes, I receive a new line in my DB with the number of press on the button (counter !).
I received a message with date / number of press without the delta because it's a counter.

Example
01-01-2017 / 10 =>it means 10 press on this date
02-01-2017 / 15 => it means 5 press (15 - 10)
03-01-2017 / 25 => it means 10 press (25 - 15)
...

Have you got an idea or a simple method to allow me to have the number of press for each day using only PowerBI ?
Other point to mention is that this counter restart to 0 when it arrives at 4095...

Thank you for your ideas
Cheers,
Michaël

[bblais · Accepted solution]

Delta = 
VAR EarlierDate = CALCULATE(MAX(TEST[date]), FILTER(TEST, TEST[Date] < EARLIER(TEST[date])))
VAR EarlierCumulativeTotal = CALCULATE(SUM(TEST[Cumulative Total]), ALL(TEST), TEST[date] = EarlierDate)
VAR DeltaTemp = TEST[Cumulative Total] - EarlierCumulativeTotal
RETURN IF(DeltaTemp>0, DeltaTemp, (4095-EarlierCumulativeTotal)+TEST[Cumulative Total])

[michaeldurieux · Accepted solution]

It works like a charm 🙂

Many thanks

[bblais]

Let's say your data table TEST was this:

date	Cumulative Total
1/1/2017	10
2/1/2017	15
3/1/2017	25

You can create a calculated column to get the deltas as such:

Delta = 
VAR EarlierDate = CALCULATE(MAX(TEST[date]),FILTER(TEST, TEST[Date] < EARLIER(TEST[date])))
RETURN TEST[Cumulative Total] - CALCULATE(SUM(TEST[Cumulative Total]), ALL(TEST), TEST[date] = EarlierDate)

[bblais]

For the reset at 4095, you could do something like this:

Delta = 
VAR EarlierDate = CALCULATE(MAX(TEST[date]), FILTER(TEST, TEST[Date] < EARLIER(TEST[date])))
VAR EarlierCumulativeTotal = CALCULATE(SUM(TEST[Cumulative Total]), ALL(TEST), TEST[date] = EarlierDate)
VAR DeltaTemp = TEST[Cumulative Total] - EarlierCumulativeTotal
RETURN IF(DeltaTemp>0, DeltaTemp, TEST[Cumulative Total])

This would result in the following if you have data where the amount resets:

[michaeldurieux]

Thank you to both of you.

For the moment, I choose the solution @bblais.

Your solution is partially good because in your example you missed "8 messages".

So for 5/1/2017, the delta should be 18 instead of 10 (8 messages between 4087 and 4095 + 10).

Any idea using a simple way ?

[bblais · Accepted solution]

Delta = 
VAR EarlierDate = CALCULATE(MAX(TEST[date]), FILTER(TEST, TEST[Date] < EARLIER(TEST[date])))
VAR EarlierCumulativeTotal = CALCULATE(SUM(TEST[Cumulative Total]), ALL(TEST), TEST[date] = EarlierDate)
VAR DeltaTemp = TEST[Cumulative Total] - EarlierCumulativeTotal
RETURN IF(DeltaTemp>0, DeltaTemp, (4095-EarlierCumulativeTotal)+TEST[Cumulative Total])

[michaeldurieux]

Still using line code, is it possible to indicate the device (first column) where to make the computation (according to the "date") and also take into account that a new line can be added for a device with a delta = 0 (because of no change between two receptions)?

Example of data I can receive (the last column indicates the value I want to find using computation)

  

[bblais · Accepted solution]

Try this:

DeviceDelta = 
VAR EarlierDate = CALCULATE(MAX(TEST[date]), FILTER(TEST, TEST[Date] < EARLIER(TEST[date]) && TEST[DeviceID] = EARLIER(TEST[DeviceID])))
VAR EarlierCumulativeTotal = CALCULATE(SUM(TEST[Cumulative Total]), ALL(TEST), TEST[date] = EarlierDate,TEST[DeviceID] = EARLIER(TEST[DeviceID]))
VAR DeltaTemp = TEST[Cumulative Total] - EarlierCumulativeTotal
RETURN IF(DeltaTemp>=0, DeltaTemp, (4095-EarlierCumulativeTotal)+TEST[Cumulative Total])

[michaeldurieux]

It works like a charm too 🙂

Thanks !

[michaeldurieux · Accepted solution]

It works like a charm 🙂

Many thanks

[LaurentCouartou]

Assuming the source table is called Data and the columns are named Date and Cumulated, here is my suggestion for your offsetting problem:

let
 Source = Data,
 #"Sorted Rows" = Table.Sort(Source,{{"Date", Order.Ascending}}),
 #"Added Index" = Table.AddIndexColumn(#"Sorted Rows", "Index", 0, 1),
 Previous = {0} & List.RemoveLastN(#"Added Index"[Cumulated],1),
 Columns = Table.ToColumns( #"Added Index" ) & { Previous },
 ColumnNames = Table.ColumnNames( #"Added Index" ) & {"Previous"},
 Table = Table.FromColumns( Columns, ColumnNames)
in
 Table

This will add a new column with the value of Cumulated in the previous row.

From there, you should be able to calculate the delta (also applying the logic to handle resets).