Forum Discussion

dataandme's avatar
dataandme
Frequent Visitor
2 years ago
Solved

Sales PY for Non Contiguous date range

Hi,

I was using the below calculation to find the PY Sales Value,
Sales SPLY = Calculate([Sales],SAMEPERIODLASTYEAR('sales'[Sales Date]))
but get the error SAMEPERIODLASTYEAR expects a contiguous selection when the date column is not unique, has gaps or it contains time portions becuase I dont have sales continuously everyday. 

I tried the Dateadd function, but still same issue arises. 

Is there other ways to calculate the PY measure dynamically? 

Thanks

  • Gabry's avatar
    Gabry
    2 years ago

    Hey dataandme ,

    don't you have a calendar / date table? If not, you should build one, connect it with the fact table with 1:* realationship (from 'Calendar'[Date] to 'Sales'[Sales Date]), then use this calendar table in your calculate, instead of  Calculate([Sales],SAMEPERIODLASTYEAR('sales'[Sales Date]))

    it becomes  Calculate([Sales],SAMEPERIODLASTYEAR('Calendar'[Date]))

     

    😉

2 Replies

  • Gabry's avatar
    Gabry
    Super User
    2 years ago

    Hey dataandme ,

    don't you have a calendar / date table? If not, you should build one, connect it with the fact table with 1:* realationship (from 'Calendar'[Date] to 'Sales'[Sales Date]), then use this calendar table in your calculate, instead of  Calculate([Sales],SAMEPERIODLASTYEAR('sales'[Sales Date]))

    it becomes  Calculate([Sales],SAMEPERIODLASTYEAR('Calendar'[Date]))

     

    😉