How to add 180 days to a start date

[MarcLykke]

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:

Calculated deadline = DATEADD('Cases'[Case.Startdate],180, DAY)

It works fine in most of the rows, but it seems to fail several times, and I can't figure out why. Maybe someone can help me?

Error 1: If the Calculated deadline is in the future, the row is just blank

Error 2: Most of the cases with an old startdate (before 2010) is also blank

Both of the columns are created with the same date-format.

[OwenAuger · Accepted 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:

https://dax.guide/DATEADD/

Regards,

Owen

[OwenAuger · Accepted 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:

https://dax.guide/DATEADD/

Regards,

Owen

[MarcLykke]

Thank you very much!
Is it the same logic behind DATEDIFF?

[OwenAuger · Accepted solution]

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