Help with Last Year Sales

[gmooney100]

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.

[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.

[amitchandak · Accepted solution]

@gmooney100 , 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-bd52912a5b...
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.