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!
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!