Forum Discussion

aalamos's avatar
aalamos
Frequent Visitor
5 years ago

Problem with SAMEPERIODLASTYEAR

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

Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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's avatar
      aalamos
      Frequent Visitor
      5 years ago

      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's avatar
        v-easonf-msft
        Community Support
        5 years ago

        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.