Most Recent Year Sales with Dynamic Dates

[Anonymous]

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.

[v-jayw-msft · Accepted solution]

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.

[amitchandak]

create a date table and use that

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/Winner-Topper-on-Map-How-to-Color-States-on-a-Map-wi...
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601