Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Calculate difference between values in same column

  I need to calculate the difference in mileages and quantity, to calculate the fuel consumption per vehicle and date. Can someone please help me? 
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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()
    )

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi Anonymous ,

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

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