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

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.