SAMEPERIODLASTYEAR error

[rajanimaddala]

Hi

I have sales table, product table and calendar table

I have slicers for FY, Month and Product

I need to get last year sales value for the current month selected. This is my DAX expression

Booked Sales(SET) Last Year = CALCULATE(SUM('BI vwSETSales'[Actual Revenue]), FILTER(ALL('BI vwSETSales'), 'BI vwSETSales'[Source] = "WON_OPPS" && 'BI vwSETSales'[Product Group] IN VALUES('BI vwProductGroup'[Target Group])), SAMEPERIODLASTYEAR('BI vwSETSales'[Transaction Date]))

I am gettint error as "SAMEPERIODLASTYEAR expects a contiguous selection when the date column has gaps, not unique..."

I have checked my calendar table. There are no duplicates, no gaps(dates starting from 2015-01-01.

I don't understand this error

Any sugesstion?

Regards

[v-yuta-msft]

@rajanimaddala ,

How about change your measure using dax below?

Booked Sales(SET) Last Year =
CALCULATE (
    SUM ( 'BI vwSETSales'[Actual Revenue] ),
    FILTER (
        ALL ( 'BI vwSETSales' ),
        'BI vwSETSales'[Source] = "WON_OPPS"
            && 'BI vwSETSales'[Product Group] IN VALUES ( 'BI vwProductGroup'[Target Group] )
    ),
    DATEADD ( 'BI vwSETSales'[Transaction Date], -1, YEAR )
)

Community Support Team _ Jimmy Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[rajanimaddala · Accepted solution]

Hi

Thanks for the reply. I am getting the same error "DATEADD can be used only when contiguous dates exist..."

I have used PARALLELPERIOD(BI vwSETSales[Transaction Date], -12, MONTH) and it worked

[Severofm]

Thank you very much, I've been trying for a long time to solve this error problem and with your solution of using 'PARALLELPERIOD' it was solved.

[mussaenda]

Change your transaction date to your date from calendar  table

[rajanimaddala]

Hi,

Thanks for the reply. I have tried but it is calculating SUM(vwSETSales[Actual Revenue]) from entire resutset

No filters applied.