DAX CALCULATE DATEADD only works for an interval of zero

jake32008

New Member
Joined
Apr 10, 2015
Messages
27
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.
 
Last edited:

Excel Facts

Back into an answer in Excel
Use Data, What-If Analysis, Goal Seek to find the correct input cell value to reach a desired result
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
 
Upvote 0
Hey Owen,

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

Thanks,

Jake
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,027
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top