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

Do you hate GETPIVOTDATA?
Prevent GETPIVOTDATA. Select inside a PivotTable. In the Analyze tab of the ribbon, open the dropown next to Options and turn it off

Forum statistics

Threads
1,223,894
Messages
6,175,254
Members
452,624
Latest member
gregg777

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