cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Calculation of difference of values between dates

Hello 

I'm trying to find a formula whit DAX formulas to calculate the difference of values ​​between dates.

For example I'd like to calculate the difference between date 29/10/2018 and 05/11/2018, and the result would be 9.3, or beeing able to choose another date of comparison like 26/11/2018 (result -18.62 ) in a slicer or filter.

 

Thanks in advance!

 

Capture 1.PNG

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Calculation of difference of values between dates

Hi @credondo ,

 

You need to modify @amitchandak's formula according to your data model, like this.

Measure = 
VAR x = 
CALCULATE(
    SUM('Table'[tons]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Release Date] = MAXX( ALLSELECTED('Table'), [Release Date])
    )
)
VAR y = 
CALCULATE(
    SUM( 'Table'[tons] ),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Release Date] = MINX( ALLSELECTED('Table'), [Release Date] )
    )
)
RETURN
x -y

mn7.PNG

 

Best regards,
Lionel Chen

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

 

View solution in original post

5 REPLIES 5
Highlighted
Super User IV
Super User IV

Re: Calculation of difference of values between dates

Hi @credondo ,

 

You can use DATEDIFF function in Power BI to calculate difference between 2 dates. Refer following for details:

https://docs.microsoft.com/en-us/dax/datediff-function-dax

 

If this helps and resolves the issue, appreciate a Kudos and mark it as a Solution! 🙂

 

Thanks,

Pragati





If this helps, Appreciate a KUDOS!
Did I answer your question? Mark my post as a solution!


Proud to be a Super User!




Highlighted
Super User IX
Super User IX

Re: Calculation of difference of values between dates

@credondo , Assume you have taken a slicer with two date. Prefer a date calendar

 

Try measure

Measure =
var _max = maxx(allselected('Date'),'Date'[Date])
var _min = minx(allselected('Date'),'Date'[Date])
return
calculate(sum(table[tons]),filter(All(DATE), Date[Date] =_max)) - calculate(sum(table[tons]),filter(All(DATE), Date[Date] =_min))

 

or

Measure =
var _max = maxx(allselected('Date'),'Date'[Date])
var _min = minx(allselected('Date'),'Date'[Date])
return
calculate(sum(table[tons]),filter(Allselected(DATE), Date[Date] =_max)) - calculate(sum(table[tons]),filter(Allselected(DATE), Date[Date] =_min))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Highlighted
Frequent Visitor

Re: Calculation of difference of values between dates

Also, the calculation is calculated on values of another column (in this case Tons as shown in the picture),  not to calculate how many days of months are between dates.

Highlighted
Frequent Visitor

Re: Calculation of difference of values between dates

Thanks for you response!..I'm having a problem that in the 5th and 6th line (in line 2 and 3 works fine), BI doesn't recognice the table Date, since I created this Date table as a calendar. Any ideas?

 

Capture2.PNG

 

 

Highlighted
Community Support
Community Support

Re: Calculation of difference of values between dates

Hi @credondo ,

 

You need to modify @amitchandak's formula according to your data model, like this.

Measure = 
VAR x = 
CALCULATE(
    SUM('Table'[tons]),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Release Date] = MAXX( ALLSELECTED('Table'), [Release Date])
    )
)
VAR y = 
CALCULATE(
    SUM( 'Table'[tons] ),
    FILTER(
        ALLSELECTED('Table'),
        'Table'[Release Date] = MINX( ALLSELECTED('Table'), [Release Date] )
    )
)
RETURN
x -y

mn7.PNG

 

Best regards,
Lionel Chen

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

 

View solution in original post

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors