Forum Discussion

shishir999's avatar
shishir999
Icon for Helper II rankHelper II
8 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
    8 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
Icon for Helper II rankHelper II
8 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
Icon for Community Champion rankCommunity Champion
8 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
    Icon for Helper II rankHelper II
    8 years ago

    Thanks alot for explaination.

     

    Regards

    Shishir Goel

  • shishir999's avatar
    shishir999
    Icon for Helper II rankHelper II
    7 years ago

    Hi Zubair,

     

    I need one more help here.

     

     

    In the same query, how can I restrict that my 1st min. value must remain ZERO and after that, if any changes in the value, it will calculate it, as the exisiting query working.

     

     

     

    Thanks

    Shishir G.