cancel
Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

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
Microsoft

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
PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Community News

Fabric Community News unified experience

Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors