Forum Discussion
Help with Last Year Sales
Hi,
I know that PowerBI has some time intelligence options to help me figure out sales for the last year time frame as what I am currently looking at for this year but I have yet to figure it out.
I have a calendar table that has my dates and I primarily use YearWeek so for example most of my reporting allows the user to drill from a year down to quarter or month or week.......The majority of the reports simply show all YearWeek values.
My sales data only has a sales field and not any type of Last Year sales field so I have to create it based on calendar.
I tried to use this formula:
Quantity LY = CALCULATE( SUM([Quantity]), SAMEPERIODLASTYEAR('Calendar'[Date])) when the report is displaying YearWeek as columns
See the result. It give me the incorrect Quantity LY.
Anonymous , I think you want same week last year, for that try
week Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,DAY))
or
This Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] = Max('Date'[Week]) ))
Last Year Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Week] = Max('Date'[Week])))Also, make sure
Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
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 :radacad sqlbi My Video Series Appreciate your Kudos.
2 Replies
- amitchandak
Super User
Anonymous , I think you want same week last year, for that try
week Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-364,DAY))
or
This Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year]) && 'Date'[Week] = Max('Date'[Week]) ))
Last Year Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Year]=max('Date'[Year])-1 && 'Date'[Week] = Max('Date'[Week])))Also, make sure
Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5bd4
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 :radacad sqlbi My Video Series Appreciate your Kudos. - Ashish_Mathur
Hi,
SAMEPERIODLASTYEAR() will look at the same dates in the previous year. Since week 26 of a year may/may not have the same dates in the previous year, the answer will be different.
