sameperiodlastyear only works with contiguous date selection

0
votes

I have a data warehouse. DimDate is connected to FactCost by datekey. The relationship is many to one and the cross filter direction is single.

I have defined YTD Cost measure: 

YTD Cost = TOTALYTD(sum(Cost[Amount]),'Date'[DateKey])

and then YTD last year

LY YTD = CALCULATE([YTD Cost], SAMEPERIODLASTYEAR(Cost[DateKey]))

When I try to plot YTD last year I get the following error message:

MdxScript(Model) (1,53) Calculation error in measure 'Cost'[LY YTD]: Function 'SAMEPERIODLASTYEAR' only works with contiguous date selection.

1
sql-serverpowerpivotpowerbidax

1 Answers

3
votes

It seems that the problem is that you are passing the date column in your fact table to the SamePeriodLastYear() function. I think it would be more standard to pass the key of the date table to the function.

The function returns a table of dates which are then used as a filter (and, of course the filter passes down to the fact table).

LY YTD = CALCULATE([YTD Cost], SAMEPERIODLASTYEAR('Date'[DateKey]))