Delta between values in the same column

[Razvan66]

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

[Razvan66]

Hi @danextian , here is a small sample of the data

Release no.	290		288
Deliv. Date	12.sept	Delta	05.sept
20.sept	0	-12600	12600
02.oct	21540	21540	0
04.oct	0	0	0
09.oct	23400	5460	17940

[v-yajiewan-msft · Accepted solution]

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]

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"

[v-yajiewan-msft]

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.

[danextian]

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.