Cumulative Sales to Date Same Period Last Year

[707Ute]

I need to build a measure that will give me the the  Cummulative Gross Sales for the same period last year.

I have my Gross Sales for 2020 by using Year and Gross Sales Measure.  These Gross Sales incorporate sales for the last 303 Days (Jan 1, 2020 - Oct 29, 2020)

However, I dont know how to arrive at a measure that will give me Gross Sales for the first 303 Days of 2019 and that is dynamic so tomorrow the measure will update to the first 304 Days of 2019.

I have tried:

Last Year YTD Gross Prod Sales = CALCULATE(SUM('1-Measures and Calculations'[Gross Sales USD]),SAMEPERIODLASTYEAR(DATESYTD('Date - Transaction Date'[PK_Date])))

but it is not providing the correct result.

Any help would be appreciated!

[v-jingzhang]

Hi @707Ute 

Do you want the measure to always display the Cummulative Gross Sales until today for the previous year without needing user to select a date? If so, you could also take the following measures for some reference. I use TODAY() function to get today value automatically.

TY Gross Sales = CALCULATE(SUM('Table'[Sales]),DATESBETWEEN('Calendar'[Date],DATE(YEAR(TODAY()),1,1),TODAY()))

LY Gross Sales = 
VAR lastYear = YEAR(TODAY())-1
VAR month = MONTH(TODAY())
VAR day = DAY(TODAY())
RETURN
CALCULATE(SUM('Table'[Sales]),DATESBETWEEN('Calendar'[Date],DATE(lastYear,1,1),DATE(lastYear,month,day)))

Best Regards,

Community Support Team _ Jing Zhang

If this post helps, please consider Accept it as the solution to help other members find it.

[amitchandak]

@707Ute , Try measure like

Last Year YTD Gross Prod Sales = CALCULATE(SUM('1-Measures and Calculations'[Gross Sales USD]),DATESYTD('Date - Transaction Date'[PK_Date],-1,year))

LYTD forced=
var _max1 =maxx('1-Measures and Calculations',[Sales date]) //or maxx(allselected('Date - Transaction Date'),'Date - Transaction Date'[PK_Date])
var _max = date(year(_max1)-1,month(_max1),day(_max1))
return
CALCULATE(SUM('1-Measures and Calculations'[Gross Sales USD]),DATESYTD(dateadd('Date - Transaction Date'[PK_Date],-1,year)),'Date - Transaction Date'[PK_Date]<=_max)

[nandic]

Hi @707Ute ,

In Power Query add new column:

YTD Criteria = if Date.DayOfYear([Date]) <= Date.DayOfYear(DateTime.LocalNow()) then 1 else 0

After that create a measure:

YTD final =

var _filterArea = filter(ALL('Date'),'Date'[YTD Criteria] = "1")

RETURN

TOTALYTD(SUM(Sales[Sales]),'Date'[Date],_filterArea)

This is the result:

https://drive.google.com/file/d/1TboapN7ljZEwDrRQx-r92FvdOkZy62KP/view?usp=sharing