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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Advocate II

## 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.

If you have other issues, please feel free to ask.

Best Regards,

Angelia

Advocate II

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

My date table code:

```Date =
ADDCOLUMNS (
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

Advocate II

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

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

Advocate II

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

Advocate II

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

Advocate II

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

Advocate II

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

## Helpful resources

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors