Forum Discussion

Tcorentin's avatar
Tcorentin
Frequent Visitor
3 years ago

Calculate multiple increments on cumulative value for non-regular timestamps

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:

 

NameTimestampValue
AN112/28/2022 10:22:07 PM284.1
AN133/1/2022 4:09:01 AM417.7
AN123/8/2022 3:33:05 AM809.8
AN123/8/2022 10:54:23 PM810
AN13/9/2022 7:12:04 AM852.1
AN123/9/2022 3:14:38 PM810.3
AN13/10/2022 4:49:01 AM852.3
AN123/10/2022 10:50:59 AM810.4
AN13/10/2022 10:39:49 PM852.6
AN13/12/2022 6:20:03 AM852.8
AN13/13/2022 5:27:13 AM853.1
AN13/14/2022 3:49:27 AM853.3
AN13/15/2022 12:56:18 AM853.4
AN133/16/2022 10:56:13 PM417.9
AN103/17/2022 4:12:58 AM780.5
AN123/17/2022 12:45:44 PM810.6
AN133/17/2022 10:04:04 PM418.2
AN123/18/2022 6:36:51 AM810.9
AN133/18/2022 9:56:49 PM418.4
AN13/18/2022 10:11:53 PM853.5
AN153/19/2022 6:59:45 AM1578.2
AN123/19/2022 2:32:09 PM811.1
AN133/20/2022 6:37:21 AM418.7
AN123/20/2022 8:41:55 PM811.4
AN133/21/2022 12:28:07 AM418.9
AN123/21/2022 11:46:45 AM811.6
AN133/21/2022 10:20:07 PM419.2

 

Need Help

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
      Tcorentin
      Frequent Visitor
      3 years ago

      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's avatar
        Tcorentin
        Frequent Visitor
        3 years ago

        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 ?