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 si...
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]
    )
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

      • shishir999's avatar
        shishir999
        Helper II
        7 years ago

        Thanks alot for explaination.

         

        Regards

        Shishir Goel