Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Most Recent Year Sales with Dynamic Dates

Looking to calculate sales growth, by product, over a filterable period of time (using a date slicer), by calculating total sales for the most recent year of the period, sales for the first year of the period, and then calculating the percentage difference between the two.

 

Currently using the following measures:

 

Last Year Sales = CALCULATE(SUM('Order Index'[Sales]),DATESBETWEEN('Order Index'[Date],DATEADD(LASTDATE('Order Index'[Date]),-364,DAY),LASTDATE('Order Index'[Date])))
 
First Year Sales = CALCULATE(SUM('Order Index'[Sales]),DATESBETWEEN('Order Index'[Date],FIRSTDATE('Order Index'[Date]),DATEADD(FIRSTDATE('Order Index'[Date]),364,DAY)))
 
Sales Growth = ([Last Year Sales]/[First Year Sales])-1
 
The First Year Sales formula is producing results that are either correct, or close to correct but not quite
 
The Last Year Sales formula is having varying results by product. Some prodcuts are returning perfectly correct results. Others are returning nearly correct results. And others are returning the grand total for sales (over a the 5 year life of the dataset) rather than just the most recent year.
 
Looking for help. Thanks.
Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi Anonymous ,

     

    Please create a CALENDAR table with no relationship between fact table and use it's date column as slicer

     

    CALENDAR = CALENDARAUTO()

     

    Below are the measures i made that according to you description and the result would be shown as below.

     

    First Year Sales = CALCULATE(SUM('Table'[sales]),DATESBETWEEN('Table'[date],MIN('CALENDAR'[Date]),EDATE(MIN('CALENDAR'[Date]),12)))

Last Year Sales = CALCULATE(SUM('Table'[sales]),DATESBETWEEN('Table'[date],EDATE(MAX('CALENDAR'[Date]),-12),MAX('CALENDAR'[Date])))

Sales Growth = ([Last Year Sales]/[First Year Sales])-1

     

    Pbix as attached.

     

    Best Regards,

    Jay

    Community Support Team _ Jay Wang

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

2 Replies