Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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 🙂
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.
@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))
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?
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.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
99 | |
80 | |
64 | |
58 |
User | Count |
---|---|
148 | |
111 | |
93 | |
84 | |
66 |