Forum Discussion

jksl_12's avatar
jksl_12
New Member
2 years ago
Solved

Difference between two values from seperate dates with refresh option

Hey there, 

 

I have been running into the issue that I have a dataset that consists of total values for various dates throughout the year that is reported into the same dataset. 

 

I want to set up a formula so that I take the latest date reported on and show the in or decrease in amount since last time. 

 

DateAmount
December 1st440.000
September 15th478.000
July 16th487.000

 

So in this setting, I want to take the data that will be coming on December 1st and have it subtracted with the September 15th data to show the difference. 

 

What I am however struggling with is how to make this update on its own so that next time a new date comes in how do I make sure it is the newest date that will get compared to the second to newest date?

 

There must be a clever way to write this in DAX or something, but I just really can't seem to find the correct answer to my problem.

 

Hope this question made sense, and thank you in advance.

- J

Need Help
  • dadb25's avatar
    dadb25
    2 years ago

    Hi jksl_12,

    I think you want to have this result :

    You can use this measure :

     

    Dif with Max Date =
    VAR MaxDate =
    CALCULATE ( MAX ( 'Feuil2'[Date] ), ALL ( Feuil2[Date] ) )
    VAR AmountMaxDate =
    CALCULATE ( SUM ( 'Feuil2'[Amount] ), 'Feuil2'[Date] = MaxDate )
    RETURN
    SUM ( Feuil2[Amount] ) - AmountMaxDate
     
    If a new date comes in your data, it will take its value.
    Hope it helps 😀!
    Regards

3 Replies

  • dadb25's avatar
    dadb25
    Helper I
    2 years ago

    Hi jksl_12,

    I think you want to have this result :

    You can use this measure :

     

    Dif with Max Date =
    VAR MaxDate =
    CALCULATE ( MAX ( 'Feuil2'[Date] ), ALL ( Feuil2[Date] ) )
    VAR AmountMaxDate =
    CALCULATE ( SUM ( 'Feuil2'[Amount] ), 'Feuil2'[Date] = MaxDate )
    RETURN
    SUM ( Feuil2[Amount] ) - AmountMaxDate
     
    If a new date comes in your data, it will take its value.
    Hope it helps 😀!
    Regards
    • jksl_12's avatar
      jksl_12
      New Member
      2 years ago

      Thank you that does look like very much what I want! I will give it a try tomorrow because my brain is too fried to be doing it today😅
      If it works as I suspect it will I will of course come back and give you a thanks for the help 🙂

  • dadb25's avatar
    dadb25
    Helper I
    2 years ago

    Mark my post as a solution if it helped you 😀