Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply

Delta calculation in PBI and counter

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 Smiley Happy
Cheers,
Michaël

3 ACCEPTED SOLUTIONS

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])

View solution in original post

It works like a charm 🙂

Many thanks

View solution in original post

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])

View solution in original post

9 REPLIES 9
bblais
Resolver III
Resolver III

Let's say your data table TEST was this:

dateCumulative Total
1/1/201710
2/1/201715
3/1/201725

 

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)

 

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:

 

Capture.PNG

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 ?

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])

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)

 

  value.png

 

 

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])

It works like a charm too 🙂

Thanks !

It works like a charm 🙂

Many thanks

LaurentCouartou
Solution Supplier
Solution Supplier

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).

 

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors