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

5 REPLIES 5
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

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

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.