DATEADD 'dates must be contiguous' error referencing measure in SWITCH formula

Maccer

New Member
Joined
Aug 21, 2013
Messages
19
Hi all,

First PowerPivot/Data Model post here, loving DAX but struggling with the time functions!
My model has a DateTable table marked as date table related to a Sales data table. The DateTable has a calculated column for Financial Year which I want to use as a report filter.

I have the following measures in my Sales (named GtNData) table;

Live Forecast:=SUM([Live Forecast Period])

Last Year:=if(HASONEVALUE(DateTable[Year]),CALCULATE([Live Forecast],DATEADD(DateTable[Date],-1,YEAR)),BLANK())

Volume (1):=CALCULATE(SWITCH([Ver1Select],"Budget",[Budget],"Live Forecast",[Live Forecast],"Locked Forecast",[Locked Forecast],"Previous Forecast",[Previous Forecast],"Last Year",[Last Year],"Actual",[Actual]),FILTER(GtNData,GtNData[Measure]="Total Volumes"))

The Last Year measure works when just added by itself to a report but when I try to pass the measure to the Volume (1) as part of the SWITCH formula it breaks down. If I have a single year selected in the Financial Year slicer and try to select 'Last Year' using the slicer which determines the 'Ver1Select' measure I get the error; 'DATEADD only works with contiguous date selections.'

What I can't understand (and I am still a beginner!) is why this is the case given that the Financial Year selection is a single year which does have contiguous dates, I could understand if the selection was say 2020 and 2022.

Any help most appreciated!
 

Excel Facts

Waterfall charts in Excel?
Office 365 customers have access to Waterfall charts since late 2016. They were added to Excel 2019.

Forum statistics

Threads
1,224,820
Messages
6,181,160
Members
453,021
Latest member
Justyna P

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