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 ...
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.

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"

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi Razvan66,

         

        For sorting questions, please follow the steps below:

        • Click "Transform data" to open the data in power query:

        • Then add a conditional column with the following syntax:

           

        • Next click close&apply button to return to the desktop table view, choose the ReleaseNo column, and then sort by the newly created column:

        • Lastly, we need to change the measure as shown below:
        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,REMOVEFILTERS('Table'[SortOrder]))
VAR CurrentQuantity = CALCULATE(SUM('Table'[Quantity]), 'Table'[Release No] = CurrentRelease, 'Table'[Deliv. Date] = CurrentDate,REMOVEFILTERS('Table'[SortOrder]))
VAR PreviousQuantity = CALCULATE(SUM('Table'[Quantity]), 'Table'[Release No] = PreviousRelease, 'Table'[Deliv. Date] = CurrentDate,REMOVEFILTERS('Table'[SortOrder]))
RETURN
IF(NOT(ISBLANK(PreviousRelease)), CurrentQuantity - PreviousQuantity, BLANK())

        Result for your reference:

        Best regards,

        Joyce

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