Forum Discussion

AGo's avatar
AGo
Post Patron
6 years ago

Rolling sum optimization

Hello,

 

I populated a line chart with a cumulative function:

Rolling count = SUMX(FILTER(ALLSELECTED(Tab[timestamp]),Tab[timestamp]<=MAX(Tab[timestamp])),CALCULATE(SUM(Tab[DeltaWithPreviousRowValue]),Tab[Item]="Console1", Tab[Sensor]="Counters/Cuts"))
 
For each record I calculated the column [DeltaWithPreviousRowValue], I've got also the [value] which is a event incremental counter.
I can't use time hiearchy on my axis because I need to prompt the line automatically in hours if few days are selected, and days if a long time range is selected in the date filter.
Maybe a function that subtracts always the [Value] at start time and not using [DeltaWithPreviousRowValue] can work fine? In this case I have no idea how to fix pre-calculated MIN(Tab[value]) as a variable/sort of parameter.
 
timestampItemSensorvalueRankDeltaWithPreviousRowValue
11/06/2020 17:04:12Console1Counters/Cuts241610031
11/06/2020 17:04:07Console1Counters/Cuts241510021
11/06/2020 17:04:01Console1Counters/Cuts241410011
11/06/2020 17:03:56Console1Counters/Cuts241310001
11/06/2020 17:03:50Console1Counters/Cuts24129991
11/06/2020 17:03:45Console1Counters/Cuts24119981
11/06/2020 17:03:35Console1Counters/Cuts24109971
11/06/2020 17:03:14Console1Counters/Cuts24099961
11/06/2020 17:02:53Console1Counters/Cuts24089951
11/06/2020 17:02:35Console1Counters/Cuts24079941
11/06/2020 17:02:27Console1Counters/Cuts24069931
11/06/2020 17:02:22Console1Counters/Cuts24059921
11/06/2020 17:02:18Console1Counters/Cuts24049911
11/06/2020 17:02:15Console1Counters/Cuts24039902
11/06/2020 17:02:09Console1Counters/Cuts24019891
11/06/2020 17:02:05Console1Counters/Cuts24009882
11/06/2020 17:01:59Console1Counters/Cuts23989871
11/06/2020 17:01:56Console1Counters/Cuts23979862
11/06/2020 17:01:50Console1Counters/Cuts23959852
11/06/2020 17:01:44Console1Counters/Cuts23939841
11/06/2020 17:01:40Console1Counters/Cuts23929832
11/06/2020 17:01:34Console1Counters/Cuts23909821
11/06/2020 17:01:31Console1Counters/Cuts23899812
11/06/2020 17:01:25Console1Counters/Cuts23879802
11/06/2020 17:01:19Console1Counters/Cuts23859791
11/06/2020 17:01:15Console1Counters/Cuts23849782
11/06/2020 17:01:09Console1Counters/Cuts23829771
11/06/2020 17:01:06Console1Counters/Cuts23819762
11/06/2020 17:01:00Console1Counters/Cuts23799752
11/06/2020 17:00:54Console1Counters/Cuts23779741

 

Many thanks in advance!

 
cumulative
DAX
rolling

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    AGo , a New column like

    maxx(filter(Table, table[timestamp] <earlier(table[timestamp] )),table[timestamp]) -table[timestamp]

    • AGo's avatar
      AGo
      Post Patron
      6 years ago

      No, I'm sorry, the resulting value I need in my measure is not from timestamp column, it has to be extracted from calculations based on [vaue] or [DeltaWithPreviousRowValue] depending on which one is easier to use.

  • sturlaws's avatar
    sturlaws
    Resident Rockstar
    6 years ago

    Hi AGo 

     

    I don't get what your issue is. Could you create a sample report and share it?

     

    Cheers,
    Sturla