Forum Discussion

alexSPY's avatar
alexSPY
Icon for Helper II rankHelper II
9 years ago
Solved

Prior year, year to date sales

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...
Need Help
  • alexSPY's avatar
    alexSPY
    9 years ago

    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's avatar
DataMonkey101
Frequent Visitor
8 years ago

This worked perfectly! Thanks! 

 

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

alexSPY's avatar
alexSPY
Icon for Helper II rankHelper II
8 years ago

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!