Calculation of difference of values between dates

[Anonymous]

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!

[Syndicate_Admin]

In my case I have this database and I would like to make a measure that gives me how many days it has taken to carry out the management.

I don't know if anyone can help me 🙂

[Anonymous]

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.

[amitchandak]

@Anonymous , 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))

[Anonymous]

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?

[v-lionel-msft]

Hi @Anonymous ,

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

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.

[Pragati11]

Hi @Anonymous ,

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