DAX Last Year Sales

[minimynt]

Hi,

I want to create LY Sales calculation, but somehow there's something wrong with the result.

The total of LY Sales is right, but I don't have any idea why LY sales for each year is not showing.

Here is my formula

LY Sales = CALCULATE([Total Sales], DATEADD(Orders[Order Date],-1,YEAR))

Does anybody know why? 😞

Thanks,

Priscilla

[jat75 · Accepted solution]

Hello, try this instead: LY Sales = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Orders'[Order Date]))

If you have a dedicated date table, you'll want to use the date column from there instead of Order Date

[sure19]

Hi,

first i have filtered year, then inserted region and sales, when i try to use the above to calculate last year sales, it gives me blank.  is it because i have already filtered the year?  how can i still show last year sales against this year sales?

thanks in advance

Suresh

[vicks]

Hi Suresh;

Did you solved your problem?, i have same issue about that, the formula didnt worked when i filtered by Year;

i have single fact table with sales measure, person dimension, Year, Month, Date(dateSQL) Dimension;

warm regards;

Opik

[jat75]

Is your date column continuous? If you're using a seperate date table, did you create the relationship using 2 fields with a Date data type?

[jat75 · Accepted solution]

Hello, try this instead: LY Sales = CALCULATE([Total Sales], SAMEPERIODLASTYEAR('Orders'[Order Date]))

If you have a dedicated date table, you'll want to use the date column from there instead of Order Date

[minimynt · Accepted solution]

@jat75 Hi, I've also tried with SAMEPERIODLASTYEAR but the result is the same. However, I just figured out that only continuous date is valid for this case and my OrderDate isn't continuous so that's why.

Thanks anyway! 🙂

[minnu]

Hi Guys, 

I know it's quite irritating to get the prior year meaures. I tried looking everywhere left with no choice than to try all by myself. Please find the solution below 

LY Sales = CALCULATE([Total Sales],Calender[Year] = YEAR(TODAY()) - 1)

Total Sales = Your measure 

Calender = Your date table

I am just subtracting 1 from the current years number in this expression YEAR(TODAY()) - 1). 

Please accept it as a solution if it resolved your issue