SAMEPERIODLASTYEAR' only works with contiguous date selections.

[MarkCBB]

Hi there,

I have the following DAX Functions and all sems to work well, however when I cross highlight on a specific region, I get the following error:
(PBI Desktop)

Error Message:


MdxScript(Model) (1, 66) Calculation error in measure 'A_MEASURES'[LYTD Value]: Function 'SAMEPERIODLASTYEAR' only works with contiguous date selections.

Below are the DAX functions:

TYTD Value = TOTALYTD ([Total Value];MAIN_SALES[DATE])

Total Value = SUM(MAIN_SALES[VALUE])

TYTD Growth Value = DIVIDE([TYTD Value]-[LYTD Value];[LYTD Value];0))

TYTD Value = TOTALYTD ([Total Value];MAIN_SALES[DATE])

LYTD Value = TOTALYTD ([Total value]; SAMEPERIODLASTYEAR( MAIN_SALES[Date]))

I have 3 Charts, Region, Product, Store.
When I click (Cross highlight) one of the items in the Region Chart the Product chart throws out that error, but the strange thing is that the Store chart doesnt.

Any Idea how to get past this?

I have been told to use a Calendar table, however when I change the MAIN_SALES[DATE] to Calendar[DATE] I get completely incorrect results. The Calendar Table has a relationship to the DATE in the MAIN_SALES Table (Date to Date). The MAIN_SALES table does not have alot of dates and there are gaps between the dates. The Calendar table that I created starts from the Year 1990 to 2030.

What I doing wrong?

 

[anvg]

Hi
Maybe https://www.sqlbi.com/articles/time-intelligence-in-power-bi-desktop/ can help you.
Regards,

 

[akice]

If you created a separate contiguous Calendar table and have it linked to MAIN_SALES[Date] like you note above, are you using columns from the Calendar table in rows/columns/filters/slicers (and not MAIN_SALES[Date])? If so then it should work. As commented before, time intelligence functions in Dax work by sliding the range of dates visible in the current filter context. Because of that many functions require contiguous dates (which is why you are getting an error message). But if using complete Calendar table, you shouldn't get this problem.