Calculate multiple increments on cumulative value for non-regular timestamps

[Tcorentin]

Hello,

I've been struggling with this one for a while.

I have a table of 3 columns.

1. A list of asset names
2. A timestamp when a specific value was recorded
3. The recorded value

My table shows the recorded value at a specific time for that asset.

I have the same name multiple time as I have many records for that asset.

The timestamp are not regular. it could be multiple time per day, or none in a week. 

The values are cumulative per asset. I can have the same value for the same asset multiple time if I get a new recorded time with the same value.

I am looking to do a measure that calculates for each asset, and per day, the increment compared to the last recorded value.

For one asset:

• if I have 1 value for a day, I want that value minus the previous recorded value
• if multiple values on the same day, I want the max on that day, minus the previous recorded value.
• if no value on the day, I want the previous recorded value.

I tried to create a new column, but my list is so big powerbi crashes before finishing the calculation (even increasing the allocated memory). I think that the measure is the only option.

If anyone could help, that would be amazing !

Thank you beforehand

Here's a portion of my table:

Name	Timestamp	Value
AN11	2/28/2022 10:22:07 PM	284.1
AN13	3/1/2022 4:09:01 AM	417.7
AN12	3/8/2022 3:33:05 AM	809.8
AN12	3/8/2022 10:54:23 PM	810
AN1	3/9/2022 7:12:04 AM	852.1
AN12	3/9/2022 3:14:38 PM	810.3
AN1	3/10/2022 4:49:01 AM	852.3
AN12	3/10/2022 10:50:59 AM	810.4
AN1	3/10/2022 10:39:49 PM	852.6
AN1	3/12/2022 6:20:03 AM	852.8
AN1	3/13/2022 5:27:13 AM	853.1
AN1	3/14/2022 3:49:27 AM	853.3
AN1	3/15/2022 12:56:18 AM	853.4
AN13	3/16/2022 10:56:13 PM	417.9
AN10	3/17/2022 4:12:58 AM	780.5
AN12	3/17/2022 12:45:44 PM	810.6
AN13	3/17/2022 10:04:04 PM	418.2
AN12	3/18/2022 6:36:51 AM	810.9
AN13	3/18/2022 9:56:49 PM	418.4
AN1	3/18/2022 10:11:53 PM	853.5
AN15	3/19/2022 6:59:45 AM	1578.2
AN12	3/19/2022 2:32:09 PM	811.1
AN13	3/20/2022 6:37:21 AM	418.7
AN12	3/20/2022 8:41:55 PM	811.4
AN13	3/21/2022 12:28:07 AM	418.9
AN12	3/21/2022 11:46:45 AM	811.6
AN13	3/21/2022 10:20:07 PM	419.2

[v-stephen-msft]

Hi @Tcorentin ,

Here's my solution.

1.Create a date column and a rank column by calcualted columns.

Date = [Timestamp].[Date]

rank = RANKX(FILTER('Table',[Name]=EARLIER('Table'[Name])),[Timestamp],,ASC,Dense)

2.Create three measures.

CurrentMax = CALCULATE(MAX('Table'[Value]),FILTER(ALLSELECTED('Table'),[Name]=MAX('Table'[Name])&&[Date]=MAX('Table'[Date])))

PreviousValue = CALCULATE(MAX('Table'[Value]),FILTER(ALLSELECTED('Table'),[rank]=MAX('Table'[rank])-1&&[Name]=MAX('Table'[Name])))

Increment = [CurrentMax]-[PreviousValue]

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.           

[Tcorentin]

Thank you very much for your help.

I just have three points:

• I would like to see the values for everyday. If there are no recorded value, then I want my increment to be of 0. Currently, for days with no values, the date doesn't even show up.
  
• The first value for every asset is a full value (as it doesn't have a previous value). Because some asset have their first recorded value at very different time, that upsets my overall analysis as I get high values for one day when I want to calculate average and max per day. How could we make the first value per asset 0 ? 

• When I try to graph for all names, I have negatives increments (see visual), how can we explain that as the individual increments are all positive ?

thank you again ! 

[Tcorentin]

Regarding negative increments. I did some troubleshooting and realized that my dataset have some 0 values in the middle.

that means that my increments goes down and back up:

it happens quite a lot. I updated the Increment measure:

Increment = IF(OR([CurrentMax]=0,[PreviousValue]=0),0,[CurrentMax]-[PreviousValue])

my new total increment per day is now all negative 😞 how can I explain that ?