Forum Discussion

shishir999's avatar
shishir999
Helper II
7 years ago
Solved

Calculate Difference between change in value

Hi,

 

In general, we calculate the Cummulative Total of the values, but my requirement is to get the each day change in value based on Cummulative values. See the attachement. 

 

 

Left side table is my Main dataset and right side one are the expected result.

How to calculate the change in values based on Running Total, as see the GREEN Color field.

Also, this could be a running total for all Category as well.

 

Thanks

Shishir

 

 

Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    7 years ago

    shishir999

     

    Try this pattern

     

    Calc col =
[Value]
    - MINX (
        TOPN (
            1,
            FILTER ( Table1, [Ctg] = EARLIER ( [Ctg] ) && [Date] < EARLIER ( [Date] ) ),
            [Date], DESC
        ),
        [Value]
    )

5 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Community Champion
    7 years ago

    shishir999

     

    Try this pattern

     

    Calc col =
[Value]
    - MINX (
        TOPN (
            1,
            FILTER ( Table1, [Ctg] = EARLIER ( [Ctg] ) && [Date] < EARLIER ( [Date] ) ),
            [Date], DESC
        ),
        [Value]
    )
    • shishir999's avatar
      shishir999
      Helper II
      7 years ago

      Thanks Zubair for the solution. 

       

      I was trying this earlier: 

      *********************** 

      Var Mx = CALCULATE(MAXX(table, [Value]),
      FILTER(table,
      [Date] <= EARLIER([Date])
      ))
      Var Mi = CALCULATE(MAXX(Table,[value]),
      FILTER(table,
      [Date] = MIN([Date])
      ))
      Var diff = Mx- Mi
      Return
      diff

      ***************************  

       

      Could you please help me to understand the Top 1 use..

       

      Thanks

      Shishir

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Community Champion
        7 years ago

        shishir999

         

        This is how it works for each row to get the previous Cumulative Value

         

        1) First Filter gets a filtered table with all dates less than current row date

        2) TOPN on this Filtered Table gets the lastrow from the Filtered Table

        3) MINX is used to get the Value on this Date

         

        so basically the flow is MINX<<<TOPN<<<<Filter