Prior year, year to date sales

[alexSPY]

It seems that many people are struggling to accomplish this. I'm trying to show a comparison in a table with this year's sales, and last year over the same time. This year is simple, but last year is proving to be difficult because my sales table doesn't have continguous dates, so I can't use SAMEPERIODLASTYEAR.

I have created the following calcualtion which correctly shows the total in the previous year through the current month, but shows the entire month, not to-date:

PY YTD Net Sales =
CALCULATE(
SUM(Sales[NET SALES]),
FILTER(
ALL('Date2'),
Date2[YEAR] = (YEAR(NOW())-1)
&& Date2[MONTH NUMBER] <= MONTH(NOW())
&& Date2[DAY] <= MAX(Sales[Day])
)
)

As you can see, I have a table created with continguous dates, 'date2', and have a relationship establish between this table, and my sales table (one to many relationship).

[Anonymous]

Hi,

https://powerpivotpro.com/2016/01/year-to-date-in-previousprior-year/

[alexSPY · Accepted solution]

I was able to build a solution using the following measure:

PY YTD Net Sales =
CALCULATE(
SUM(Sales[NET SALES]),
SAMEPERIODLASTYEAR(
DATESYTD(Date2[Date])
),
FILTER(
ALL(Date2),
Date2[MONTH NUMBER] <= (MONTH(NOW())-1)
)
)+
CALCULATE(
SUM(Sales[NET SALES]),
SAMEPERIODLASTYEAR(
DATESYTD(Date2[Date])
),
FILTER(
ALL(Date2),
Date2[MONTH NUMBER] = MONTH(NOW())
&& Date2[DAY] <= DAY(NOW())
)
)

The key was adding in the current month as a separate function. Without this, I was getting the sum of an entire months sales in the current month for the previous year.

[DataMonkey101]

This worked perfectly! Thanks! 

Now how would I do the same thing for Prior Year, Month to Date instead of year to date?

[alexSPY]

Hi @DataMonkey101!

The key is to use VAR equations. I've worked on this one extensively with the Microsoft team, and this equation both simplifies the measure, and runs faster.

CY MTD NET SLS = 
var monthNum = MONTH(TODAY())
var THISYEAR = YEAR(TODAY())
return CALCULATE(
	SUM(Sales[NET SALES]), 
	FILTER(
		ALL('Date'), 
			[YEAR]=THISYEAR && 
			'Date'[MONTH NUMBER]=monthNum
	)
)

Hopefully this is helpful! You can use this same VAR set up to calculate YTD, PY YTD - change the VAR THIS YEAR to Year(Today())-1, MTD, PY MTD etc.

Enjoy!

[alexSPY]

I have dug into this further, and I suspect something is wrong with my date table, but I can't identify what. Here's how I created my date table:

Date2 = CALENDAR(DATE(YEAR(MIN(Sales[DOCDATE])),1,1),DATE(YEAR(MAX(Sales[DOCDATE])),12,31))

When I examine this table, I see that it correctly is building the date ranges, beginning January 1, 2013 (before my earliest date on my sales table), and ending December 31, 2017 (after the last date on my sales table). I created a new measure to calculate prior year year-to-date sales:

PY YTD Net Sales =
CALCULATE(
SUM(Sales[NET SALES]),
DATESINPERIOD(
Date2[Date],
LASTDATE(Date2[Date]),
-1,
YEAR
)
)

Strangely, the results of this calculation return the current year-to-date totals, not last year. Any ideas why?

[Anonymous]

In your formula:

Date2[DAY] <= MAX(Sales[Day]) 

is problematic since it will only take dates whose DAY is less than the day chosen for ALL months.

If you have created a continous Date table - use it (Date2[Date]) in SAMEPERIODLASTYEAR function instead of Sales table

Michael

[alexSPY]

Hi @Anonymous,

Thanks for the suggestion. Here is the formula I come up with:

PY YTD Net Sales =
CALCULATE(
SUM(Sales[NET SALES]),
SAMEPERIODLASTYEAR('Date'[Date])
)

When I do this though, I get the total for ALL years, not just last year. Do you know why?

[Anonymous]

When you slice/filter your report - do you filter by Year column from the "DATE" table or from "Sales" Table?

[alexSPY]

I can slice and filter with either table, and get the correct response. For example, if I drop Date from the 'date2' table into a visual level filter, I can successfully filter my 'sales' table this way. Was this what you were inquiring about?

[Anonymous]

Yes. You should use the Date table for filtering/showing in your report.

So it is working?

[alexSPY]

Correct. If I create a filter/slicer using the 'Date2' table, I am able to achieve the correct results.

[Anonymous]

Ok. Glad it works

[alexSPY]

Hi @Anonymous, sorry to confuse you. If I use a visual, or page slicer, I can correctly filter the information, but this doesn't fix the DAX equation.

Do you know how to fix the DAX equation so that it filters correctly?

[Anonymous]

Sorry, I don't get what the problem is exactly.

Can you attach screenshot with the problem?

Michael

[alexSPY]

Sorry to confuse. Here's the challenge:

The following DAX calculation correctly returns the total for each month, up to the current month, but not month-to-date:

PY YTD Net Sales =
CALCULATE(
SUM(Sales[NET SALES]),
FILTER(
ALL('Date2'),
Date2[YEAR] = (YEAR(NOW())-1)
&& Date2[MONTH NUMBER] <= MONTH(NOW())
&& Date2[DAY] <= MAX(Sales[Day])
)
)

You then suggested that I use a SAMEPERIODLASTYEAR function since I have a continugious date table. So I used this calcualtion:

PY YTD Net Sales =
CALCULATE(
SUM(Sales[NET SALES]),
SAMEPERIODLASTYEAR('Date'[Date])
)

This calculation returns the sum of each month, for all years. I have data that goes back to 2013, so this calcualtion is aggregating ALL sales in the same period (January through May). I unfortunately can't submit a screen shot as I'm working with private company information.

[Adman]

Hi AlexSPY

I seem to have the same issue as you have when comparing the current years total year to date to the prior year (NWAU is my versons of SALES!

NWAU Prior_YTD = CALCULATE ('HIE Data'[NWAU YTD], DATEADD ('datekey'[Date], -1, YEAR  ) )

NWAU YTD = TOTALYTD ( SUM ('HIE Data'[nwau_final]),'datekey'[Date],ALL(datekey[Year]), "06/30" )

the "NWAU Prior_YTD" column isn't picking up the prior year any ideas why?

THanks Adman

[Adman]

I've adapted the table formula to

PY YTD NWAU =CALCULATE(SUM('HIE Data'[nwau_final]),SAMEPERIODLASTYEAR(DATESYTD(DateKey[Date])),FILTER(ALL(DateKey),DateKey[MONTH NUMBER] <= (MONTH(NOW())-1)))+CALCULATE(SUM('HIE Data'[nwau_final]),
SAMEPERIODLASTYEAR(DATESYTD(DateKey[Date])),FILTER(ALL(DateKey),DateKey[MONTH NUMBER] = MONTH(NOW())&& DateKey[DAY] <= DAY(NOW())))

I get the following message "DAX comparison operations do not support comparing values of type Text with values of type Integer. Consider using the VALUE or FORMAT function to convert one of the values"

have checked an there are no values of type text.. any ideas  Thanks Adman

[alexSPY]

Hi @Adman,

I assume that this is because one of your columns has data in it that is type "text", though you have indicated that you have checked this. Because I can't see your dataset, unfortunately, I don't have any other suggestions, but I would recommend that you double check you date table to ensure that the columns in your date table are also formatted as numbers, and not text:

This is from my dataset, and you'll notice that for my "month number" column, it's formatted as a whole number.