Calculate difference between values in same column

[VLoyen]

I need to calculate the difference in mileages and quantity, to calculate the fuel consumption per vehicle and date. Can someone please help me? 

[VLoyen · Accepted solution]

I found the solution! 

thought I'd share it with you, sice you've all been so helpfull. 

Gereden km's tussen tankbeurten =
VAR CurrentVehicle = 'F: Tankgegevens'[Voertuignummer]
VAR CurrentDate = 'F: Tankgegevens'[Datum]
VAR CurrentKM = 'F: Tankgegevens'[KM-stand]

VAR PreviousKM =
CALCULATE(
MAX('F: Tankgegevens'[KM-stand]),
FILTER(
'F: Tankgegevens',
'F: Tankgegevens'[Voertuignummer] = CurrentVehicle &&
'F: Tankgegevens'[Datum] < CurrentDate
)
)

RETURN
IF(
NOT ISBLANK(PreviousKM),
CurrentKM - PreviousKM,
BLANK()
)

[Ashish_Mathur]

Hi,

Share the download link of the PBI file.

[v-kaiyue-msft]

Hi @VLoyen ,

Please follow these steps:
1. For the first column of your expected result, you can create the metric and write the following expression.

diff =

VAR _date = SELECTEDVALUE('Table'[date ])

VAR _maxdate = CALCULATE(LASTNONBLANK('Table'[date ],CALCULATE(SUM('Table'[mileage]))),FILTER(ALLSELECTED('Table'),'Table'[vehicle] = MAX('Table'[vehicle]) && 'Table'[date ] < _date))

VAR _per = IF(NOT(ISBLANK(SUM('Table'[mileage]))),

CALCULATE(SUM('Table'[mileage]),FILTER(ALLSELECTED('Table'),'Table'[date ] = _maxdate)),

BLANK())

RETURN

IF(_per = BLANK(),BLANK(),MAX('Table'[mileage]) - _per)

2.The result obtained is shown below.

For the second column of your expected result, I'm not really sure how I got it, I tried to calculate it in several ways, but all of them are different from your expected result, could you please describe again in detail how to get this result.

If your Current Period does not refer to this, please clarify in a follow-up reply.

Best Regards,

Clara Gong

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

[VLoyen]

At VAR _per i receive the following error:The syntax for 'VAR' is incorrect. (DAX(VAR _date = SELECTEDVALUE('F: Tankgegevens'[Datum])VAR _maxdate = CALCULATE(LASTNONBLANK('F: Tankgegevens'[Datum],CALCULATE(SUM('F: Tankgegevens'[KM-stand]))),FILTER(ALLSELECTED('F: Tankgegevens'),'F: Tankgegevens'[Voertuignummer] = MAX('F: Tankgegevens'[Voertuignummer] && 'F: Tankgegevens'[Datum] < _date))VAR _per = IF(NOT(ISBLANK(SUM('F: Tankgegevens'[KM-stand]))),CALCULATE(SUM('F: Tankgegevens'[KM-stand]),FILTER(ALLSELECTED('F: Tankgegevens'),'F: Tankgegevens'[Datum] = _maxdate)),BLANK())RETURNIF(_per = BLANK(),BLANK(),MAX('F: Tankgegevens'[KM-stand]) - _per))). 

[BIswajit_Das]

Hello @VLoyen  can you provide an example of your expected result.

[VLoyen]

vehicle	date	quantity	mileage	diff in mileage	consumption
8374	28/09/2023	46,6	99212
8374	2/10/2023	29,06	99605	393	13,52374398
8374	5/10/2023	30,09	99970	365	12,13027584

first, I need the difference in mileage (per vehicle number!/date), so i can make a measure for the consumption (difference in mileage/quantity). 

Is that enough info?

[miTutorials]

Please try the below measure, change the table name accordingly.

Difference in Mileage = VAR Off_ = CALCULATE(SUM('Table (2)'[mileage]),OFFSET(-1,ALLSELECTED('Table (2)'[date ]),ORDERBY('Table (2)'[date ])))
VAR Cur_ = CALCULATE(SUM('Table (2)'[mileage]),OFFSET(0,ALLSELECTED('Table (2)'[date ]),ORDERBY('Table (2)'[date ])))

Return IF(ISBLANK(Off_),BLANK(),  Cur_ - Off_)

A detailed video on this functions are on my channel, do check it out.

New Dax Functions in PowerBI | Index, Offset & Window | MiTutorials (youtube.com)

Regards

Ismail 

[VLoyen]

The measure doesn't give any errors, but it doesn't work.