cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
minimynt
Frequent Visitor

DAX Last Year Sales

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.

Untitled.png

Here is my formula

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

Does anybody know why? 😞

 

 

Thanks,

Priscilla

2 ACCEPTED SOLUTIONS
jat75
Advocate II
Advocate II

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

View solution in original post

minimynt
Frequent Visitor

@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! 🙂

View solution in original post

6 REPLIES 6
sure19
Helper II
Helper II

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
Frequent Visitor

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

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
Advocate II
Advocate II

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
Frequent Visitor

@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! 🙂

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

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors