DAX CALCULATE DATEADD only works for an interval of zero

[jake32008]

I'm receiving this error both in a larger data model and a simplified model.

I have two tables in the data model, a 'quantity' table with a date column containing the first date of each month in a year and an associated quantity and a contiguous three-year 'calendar' table that extends a year prior to and after the dates in quantity table.

I have created a relationship between the date column in the quantity table and the data column in the calendar table.

I have also marked the calendar table as a date table.

In a calculated column of the data model I have entered the following formula to retrieve the quantity for the prior month:

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;"> =CALCULATE(SUM(quantity[quantity]), DATEADD(calendar[date], -1, MONTH))

</code>I have also tried the PREVIOUSMONTH() equivalent:

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;"> =CALCULATE(SUM(quantity[quantity]), PREVIOUSMONTH(calendar[date]))

</code>Both of these calculations, however, only returns blanks.

If I set the DATEADD interval to zero, the calculation will return the same month's quantity as expected.

<code style="margin: 0px; padding: 0px; border: 0px; font-style: inherit; font-variant: inherit; font-weight: inherit; font-stretch: inherit; line-height: inherit; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; vertical-align: baseline; white-space: inherit;"> =CALCULATE(SUM(quantity[quantity]), DATEADD(calendar[date], 0, MONTH))

</code>Thank you in advance for your help here.

 

[Ozeroth]

Hi there,

From your description, it sounds like you're creating a calculated column with these formulas - is that right?

You should create measures rather than calculated columns in this situation. You would be getting blanks because of conflicting date filters if your calculated column is in the quantity table (current month from quantity table vs previous month from calendar table).
https://exceleratorbi.com.au/calculated-columns-vs-measures-dax/

If that's not the problem, could you post back with a sample model or the structure of tables/relationships?

Regards,
Owen

 

[jake32008]

Hey Owen,

Yes, I am creating a calculated column. Thanks for your feedback, I'll adjust and respond here shortly.

Thanks,

Jake

 

[jake32008]

Hey Owen,

Thanks again for your response here. This got me re-thinking my model and I was able to re-build the model in a much simpler yet robust manner that works as expected using measures instead of calculated columns.

Jake