Forum Discussion

Razvan66's avatar
Razvan66
Frequent Visitor
1 year ago
Solved

Delta between values in the same column

Hello together,

 

Have a question about a calculation.

 

So, i have this matrix, what im intrested to do i a delta between each release numbers to calculate the difference. Now the problem is that quantities come from the the column and there is also the deliv date to take into consideration, something like IF release no = earlier release no AND deliv. date (release 290) = deliv.date (release 288) ==> delta

Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Razvan66 ，hello danextian，thank you for your prompt reply!

     

    Please check the following measure:

     

    Delta Between Versions = 
VAR CurrentRelease = SELECTEDVALUE('Table'[Release No])
VAR CurrentDate = SELECTEDVALUE('Table'[Deliv. Date])
VAR PreviousRelease = CALCULATE(MAX('Table'[Release No]), 'Table'[Release No] < CurrentRelease, 'Table'[Deliv. Date] = CurrentDate)
VAR CurrentQuantity = CALCULATE(SUM('Table'[Quantity]), 'Table'[Release No] = CurrentRelease, 'Table'[Deliv. Date] = CurrentDate)
VAR PreviousQuantity = CALCULATE(SUM('Table'[Quantity]), 'Table'[Release No] = PreviousRelease, 'Table'[Deliv. Date] = CurrentDate)
RETURN
IF(NOT(ISBLANK(PreviousRelease)), CurrentQuantity - PreviousQuantity, BLANK())

     Result:

    Best regards,

    Joyce

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

5 Replies

  • danextian's avatar
    danextian
    Super User
    1 year ago

    Hi Razvan66 ,

     

    As always, please post a workable sample data (not an image) that can be copy-pasted to Excel. A link to Excel file in the cloud will do.

  • Razvan66's avatar
    Razvan66
    Frequent Visitor
    1 year ago

    Hi danextian , here is a small sample of the data

     

    Release no.290 288
    Deliv. Date12.septDelta05.sept
    20.sept0-1260012600
    02.oct21540215400
    04.oct000
    09.oct23400546017940
    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi Razvan66 ，hello danextian，thank you for your prompt reply!

       

      Please check the following measure:

       

      Delta Between Versions = 
VAR CurrentRelease = SELECTEDVALUE('Table'[Release No])
VAR CurrentDate = SELECTEDVALUE('Table'[Deliv. Date])
VAR PreviousRelease = CALCULATE(MAX('Table'[Release No]), 'Table'[Release No] < CurrentRelease, 'Table'[Deliv. Date] = CurrentDate)
VAR CurrentQuantity = CALCULATE(SUM('Table'[Quantity]), 'Table'[Release No] = CurrentRelease, 'Table'[Deliv. Date] = CurrentDate)
VAR PreviousQuantity = CALCULATE(SUM('Table'[Quantity]), 'Table'[Release No] = PreviousRelease, 'Table'[Deliv. Date] = CurrentDate)
RETURN
IF(NOT(ISBLANK(PreviousRelease)), CurrentQuantity - PreviousQuantity, BLANK())

       Result:

      Best regards,

      Joyce

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

      • Razvan66's avatar
        Razvan66
        Frequent Visitor
        1 year ago

        Hello,

         

        Thank you very much for the solution, it works.

         

        But now another issue, if i sort the column, the release no.`s are descending 290, 288 ..., its not working anymore. Did this by "sort by column"