Forum Discussion

IT-Joe's avatar
IT-Joe
New Member
6 years ago
Solved

PREVIOUSYEAR FUNCTION ++

Our business has a trienial cycle and I need to compare current sales to sales of 3 years prior. The PreviousYear function will do last year, but how can I get it to work for the same period 3 years ago? Also, will it acccount for leap years? Any help is much appreciated!

 

Need Help
  • amitchandak's avatar
    amitchandak
    6 years ago

    Try

    CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR(dateadd('Date'[Date Filer],-3,YEAR)))

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Try

    CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR(dateadd('Date'[Date Filer],-3,YEAR)))
  • v-xicai's avatar
    v-xicai
    Community Support
    6 years ago

    Hi IT-Joe ,

     

    You can create calendar table first of all, then create relationship between your data table and calendar table on date field.

     

    Calendar = CALENDAR(MIN(Table1[Date]),MAX(Table1[Date]))  
 
Or
 
Calendar =CALENDARAUTO()

    Secondly, you may create measure using DAX below.

     

    Measure1= CALCULATE(SUM(Table1[Sales]),DATEADD('Calendar'[Date],-3,YEAR))

    Best Regards,

    Amy

     

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