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.
Hi everyone
I am trying to do a very simple excercise where I want to add 180 days to a start date by using the following DAX to create a calculated column:
Solved! Go to Solution.
Hi @MarcLykke
If you are just wanting to add 180 days to a scalar date value, I would recommend you just add 180:
Calculated deadline =
'Cases'[Case.Startdate] + 180
DATEADD is not suitable for what you are doing here, i.e. adding or subtracting from a scalar date value. It's a table function which returns a column of date values by shifting an initial set of values by a specified number of periods. It is often used for modifying date filters as part of a measure calculation. It just so happens that it can return a single-date table which is converted to a scalar when called in a row context which contains the date you are shifting. It can only return dates that exist in the column referenced in the first argument (and includes an automatic context transition, see article linked below), which likely explains the blanks you were getting.
See here for a general description:
Regards,
Owen
You're welcome 🙂
Regarding DATEDIFF, yes, it's similar in that it operates on datetime scalars, and returns a scalar value. It calculates how many "interval boundaries" are crossed from one datetime value to another.
Using your example, if d1 & d2 are dates and d2 = d1 + 180, then DATEDIFF ( d1, d2, DAY ) = 180.
Regards,
Owen
Hi @MarcLykke
If you are just wanting to add 180 days to a scalar date value, I would recommend you just add 180:
Calculated deadline =
'Cases'[Case.Startdate] + 180
DATEADD is not suitable for what you are doing here, i.e. adding or subtracting from a scalar date value. It's a table function which returns a column of date values by shifting an initial set of values by a specified number of periods. It is often used for modifying date filters as part of a measure calculation. It just so happens that it can return a single-date table which is converted to a scalar when called in a row context which contains the date you are shifting. It can only return dates that exist in the column referenced in the first argument (and includes an automatic context transition, see article linked below), which likely explains the blanks you were getting.
See here for a general description:
Regards,
Owen
Thank you very much!
Is it the same logic behind DATEDIFF?
You're welcome 🙂
Regarding DATEDIFF, yes, it's similar in that it operates on datetime scalars, and returns a scalar value. It calculates how many "interval boundaries" are crossed from one datetime value to another.
Using your example, if d1 & d2 are dates and d2 = d1 + 180, then DATEDIFF ( d1, d2, DAY ) = 180.
Regards,
Owen
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 |
---|---|
41 | |
21 | |
21 | |
14 | |
13 |
User | Count |
---|---|
43 | |
32 | |
30 | |
18 | |
18 |