Problem with SAMEPERIODLASTYEAR

[aalamos]

Hi, Im having a problem with the calculation made by SAMEPERIODLASTYEAR,  but this problem does not happens using it twice to get the last last year calculation:

As you can see, the LY Revenue total calculation is absolutely wrong and I can not get why.

LY Revenue =

CALCULATE(SUM('Sales'[total]), SAMEPERIODLASTYEAR('Calendar'[Date]))

LLY Revenue = CALCULATE(SUM('Sales'[total]), SAMEPERIODLASTYEAR(SAMEPERIODLASTYEAR('Calendar'[Date])))

Thanks in advance

[amitchandak]

@aalamos , Try trailing year measure

LY Revenue =
CALCULATE(SUM('Sales'[total]), dateadd('Calendar'[Date],-1, year))

LLY Revenue =

CALCULATE(SUM('Sales'[total]), dateadd('Calendar'[Date],-2, year))

or try

LLY Revenue = CALCULATE(SUM('Sales'[total]), SAMEPERIODLASTYEAR(dateadd('Calendar'[Date],-1,year)))

[aalamos]

Hi,

I tried your solution but I have the following issue that is the same using SAMEPERIODLASTYEAR:

As you can see, if I use an advanced filter on the date slicer using date is "on or after" 01-01-2021 the calculation is correctly done:

BUT,  if I move the slicer to any other data range and then I move it back to the same range (01-01-2021 / 11-07-2021) the sum calculation is messed up again:

Thanks in advance for your time

[v-easonf-msft]

Hi,  @aalamos 

From your current formula, I did not find any error, maybe you need to  manually  filter the year in  table visual filter pane.

If I misunderstood, please explain it in more detail and share your sample files for further research.

Best Regards,
Community Support Team _ Eason
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.