cancel
Showing results 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

## Last years sales vs this year

Hello all,

I am using a formula to calculate the sales last year, but when I put it in a chart it also shows the other years...

What is it that I am not doing right?

My formula is:

```SalesYTD =
CALCULATE (
Orders[Total sales],
FILTER (
ALL ( 'Date' ),
'Date'[Year] = MAX ( 'Date'[Year] )
&& 'Date'[Dates] <= MAX ( 'Date'[Dates] )
))```

13 REPLIES 13
Employee

Hi @0xygen27,

Your formula syntax is right. But it gets the year-to-date calculation rather than last years sales. The picture shows different years and year-to-date calculations.

For last year sales, you can use SAMEPERIODLASTYEAR function, please review the following formula.

`Total Sales YTD Last Year = CALCULATE([Sales YTD],SAMEPERIODLASTYEAR('Date'[dates]))`

More details, there is a similar thread here.

Best Regards,

Angelia

Thanks Angelia! When I use your formula I still get the other years in my chart 😞

My date table code:

```Date =
CALENDAR (DATE(2009,1,1), DATE(2017,12,31)),
"DateAsInteger", FORMAT ( [Date], "YYYYMMDD" ),
"Year", YEAR ( [Date] ),
"Monthnumber", FORMAT ( [Date], "MM" ),
"YearMonthnumber", FORMAT ( [Date], "YYYY/MM" ),
"YearMonthShort",FORMAT ( [Date], "YYYY/mmm" ),
"MonthNameShort", FORMAT ( [Date], "mmm" ),
"MonthNameLong", FORMAT ( [Date], "mmmm" ),
"DayOfWeekNumber", WEEKDAY ( [Date] ),
"DayOfWeek", FORMAT ( [Date], "dddd" ),
"DayOfWeekShort", FORMAT ( [Date], "ddd" ),
"Quarter", "Q" & FORMAT ( [Date], "Q" ),
"YearQuarter", FORMAT ( [Date], "YYYY" ) & "/Q" & FORMAT ( [Date], "Q" )
)```
Employee

Hi @0xygen27,

What's your mean of  "I still get the other years in my chart". In your resource data, there are 2010,2011,2012,2013, so it will return the years in your chart. Theoretically, for 2010, the measure should get some of 2009(it should be null here), for 2011, the measure should get some of 2010 and so on.

Best Regards,
Angelia

I want to only calculate the sum of the last years sale and then put that against the sales of this year. So you get a graph with 2 lines, 1 line is this years sale and the other is last years sale.

Employee

Hi @0xygen27,

Yeah, you should add the year as axis level, the 'SalesYTD' measure you created and 'Total Sales YTD Last Year' measure I posted, you will get your expected result.

If you want get 2010 year in one line, and the last year 2009 in another line. you can add filter(Table, Table[year]=2010) in 'SalesYTD' measure, it will display two points in the line chart visual.

Best Regards,
Angelia

```SalesYTD =
CALCULATE (
Orders[Total sales],
FILTER (
ALL ( 'Date' ),
'Date'[Year] = MAX ( 'Date'[Year] )
&& 'Date'[Dates] <= MAX ( 'Date'[Dates] )
))```

Where should I add the filter (Table, Table[year]=2010) in this code? I tried it, but I got an error. My timetable is called Date, so it should be (Date,Date[year]=2010). Only wondering where in the forumula it should be placed.

Employee

Hi @0xygen27,

Please try the following formula, and check if it works.

```SalesYTD =
CALCULATE (
Orders[Total sales],
FILTER (
ALL ( 'Date' ),
'Date'[Year]=2010
&& 'Date'[Dates] <= MAX ( 'Date'[Dates] )
))```

Best Regards,
Angelia

Dear Angelia,

I tried your code for 2010 and adjusted it to 2009, but I only get the following figure on the right. The figure on the left is how I want it to be and the code on the upper right side it the code they used. The pbix file is the sample of your retail analyses (from power bi site).

Employee

Hi @0xygen27,

Got it, I thought there only year on x axis. Please try the following steps.

1. Create another table only including data, name it as 'NewTable'. Create calculated column year=Year[NewTable[date]] .

2. Create the meaure using the foluma.

```SalesYTD =
CALCULATE (
Orders[Total sales],
FILTER (
ALL ( 'Date' ),
'NewTable'[Year]=2010
&& 'Date'[Dates] <= MAX ( 'Date'[Dates] )
))```

3. Create the visual and check if it works fine.

If this is not your case, could you please share a sample data. Maybe your data is private, you can create fake data.

Best Regards,
Angelia

Hi Angelia, If I use step 1 it tells me Cannot find table 'Year'.

What kind of Sample data do you want? Sample data from the Table Date or from Orders or both? 😄

Employee

Hi @0xygen27,

Year is function in Power BI. It is not recognized in your tool. The desired sample data is like what your resource data. So I can reproduce your scenario.

Best Regards,
Angelia

This is the data I am working with, the buttom one is the table Orders the top one is the table Newtable(only dates)

Are you using a SSAS cube or tabular model?

I had same problem and I solved it by defining dimension's time properties in my cube and tabular model, then I refreshed the reports and the functions started working

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.

#### Power BI Monthly Update - June 2024

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

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors