Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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.

Here is my formula

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

Does anybody know why? 😞

Thanks,

Priscilla

2 ACCEPTED SOLUTIONS
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

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

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

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

Advocate II

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?

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

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

Anonymous
Not applicable

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors