topic Re: Measure with DateAdd returns NaN in DAX Commands and Tips

Measure with DateAdd returns NaN

Anonymous — Mon, 25 Sep 2023 13:43:47 GMT

Hi there,

I`m struggling with the measure dateAdd which delivers NaN, which I don´t understand.

The value in the table which I am searching for looks like this:

The measure which I am using is

ValuePrevMonth = (CALCULATE([Price], DATEADD(Table[Date],-1, MONTH)))

when put next to each other the first entry is there, but the second is shown as NaN

Actually, my understanding was that at least 0 should be returned. There is no calculation or division through zero, so why NaN?

In case of the dateAdd function: which key is used to do a lookup? Do I need an extra mapping to a calendar table?

Thanks in advance for any feedback.

Re: Measure with DateAdd returns NaN

ChiragGarg2512 — Tue, 26 Sep 2023 05:36:45 GMT

Anonymous , If [price] is a column, then try changing the dax to

ValuePrevMonth = (CALCULATE(sum('Table'[Price]), DATEADD('Table'[Date],-1, MONTH)))

Thank you

Re: Measure with DateAdd returns NaN

Anonymous — Tue, 26 Sep 2023 05:38:59 GMT

Thanks, but price is already a measure with sum(table[price]), my fault, I should have added this as information.

Re: Measure with DateAdd returns NaN

ChiragGarg2512 — Tue, 26 Sep 2023 06:07:40 GMT

Anonymous , Check the data type of date column. The dax seems to work fine

Re: Measure with DateAdd returns NaN

Anonymous — Tue, 26 Sep 2023 06:14:14 GMT

ChiragGarg2512 The data type of the column is already from kind date. I´m wondering if I need an index or sth like that?

Re: Measure with DateAdd returns NaN

ChiragGarg2512 — Tue, 26 Sep 2023 06:25:51 GMT

Anonymous , can some sample data for 10 to 12 rows be provided in tabular form. There shouldn't be a need to create a new column.

Re: Measure with DateAdd returns NaN

Anonymous — Thu, 28 Sep 2023 06:45:54 GMT

ChiragGarg2512 I think I have found the problem. This is the table how it looks like.

Article	Price	Delivery	Date
4251203005	23,04 €	Express GmbH TEST	01.07.2023
4251203005	23,05 €	Express GmbH TEST	01.11.2023
4251203005	23,06 €	Express GmbH	01.09.2023
4251203005	23,07 €	Express GmbH	01.10.2023
4251203005	23,08 €	Express GmbH TEST	01.08.2023

I assume the measure cannot find the entry due to the change of the name of the delivery, right? Therefore dateAdd returns NaN because there is no such entry even the date is set.

Re: Measure with DateAdd returns NaN

ChiragGarg2512 — Thu, 28 Sep 2023 08:05:48 GMT

Anonymous , if there is only price and valueprevmonth, there is no way of distinction. For each product the valueprevMonth will be different. If delivery name is added to table visual, the measure will look like this