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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
0xygen27
Advocate II
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] )
    ))

Sales YTD.png

13 REPLIES 13
v-huizhn-msft
Microsoft Employee
Microsoft 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

 

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

My date table code:Sales YTD code.png

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" )
)

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.

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.

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

 

Sales YTD third try.png

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? 😄

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

preview info date.pngpreview info.png

 

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

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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