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. A list of asset names A timestamp when a specific value was recorded The recorded value My table shows t...
Need Help
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 ?