Last years sales vs this year

[0xygen27]

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

[v-huizhn-msft]

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

[0xygen27]

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

[v-huizhn-msft]

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

[0xygen27]

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.

[v-huizhn-msft]

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

[0xygen27]

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.

[v-huizhn-msft]

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

[0xygen27]

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

[v-huizhn-msft]

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

[0xygen27]

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

[v-huizhn-msft]

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

[0xygen27]

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

[reneincer]

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