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

8 Replies

  • BIswajit_Das's avatar
    BIswajit_Das
    Impactful Individual
    2 years ago

    Hello Anonymous  can you provide an example of your expected result.

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago
      vehicledate quantitymileagediff in mileageconsumption 
      837428/09/202346,699212   
      83742/10/202329,069960539313,52374398 
      83745/10/202330,099997036512,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's avatar
        miTutorials
        Super User
        2 years ago

        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 

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    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()
    )