current year till date sales vs previous year for the same period

[tabkaz]

need to calculate a measure that could show values year-wise, the current year till date (Jan 1 - Nov 6 '20) vs previous year same period (Jan 1 - Nov 6 '19); a single measure that can show value for both years or separate measurefor 2019 & 2020. Also a % variance vs previous year

tried to use many measures but couldn't get the desired result,

GRPs MTD = VAR MaxMonth = CALCULATE(MAX('Calendar'[MonthNum]), ALL('Calendar'),'Calendar'[CurMonthOffset] = -1)

Return CALCULATE([Total GRP],'Calendar'[MonthNum] <= MaxMonth)

the above measure is calculating the sum for Jan - Oct both years, if cutoff period set to 0 it will calculate values till Nov whole month.
https://drive.google.com/file/d/1akOF13RN9nv0M-KIr7Rz0X55-DgtlTp8/view?usp=sharing 

[tabkaz · Accepted solution]

well, i think i have figured out a solution by using an offset function available on the calendar table, filtering it on the day level;

GRPs MTD = VAR MaxMonth = CALCULATE(MAX('Calendar'[day of year]), ALL('Calendar'),'Calendar'[CurweekOffset] = 0)

Return CALCULATE([Total GRP],'Calendar'[MonthNum] <= MaxMonth),FILTER(ALL('Calendar'),'Calendar'[Year] = -1)

[amitchandak]

@tabkaz , Try measures like these examples with a date table

YTD QTY forced=
var _max = today()
return
if(max('Date'[Date])<=_max, calculate(Sum('order'[Qty]),DATESYTD('Date'[Date])), blank())
//or
//calculate(Sum('order'[Qty]),DATESYTD('Date'[Date]),filter('Date','Date'[Date]<=_max))
//calculate(TOTALYTD(Sum('order'[Qty]),'Date'[Date]),filter('Date','Date'[Date]<=_max))

Last year

LYTD QTY forced=
var _max = date(year(today())-1,month(today()),day(today()))
return
if(max('Date'[Date])<=_max, CALCULATE(Sum('order'[Qty]),DATESYTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max), blank())

[tabkaz]

Thanks Amit, the measures u have posted in not giving the desired result; YTD LY is not showing any result.

[v-shex-msft]

Hi @tabkaz,

I'd like to suggest you use date function to manually define the filter range and calculate, please take a look to follow if it helps:

Time Intelligence "The Hard Way" (TITHW)  

If the above also not work for your scenario, please share some dummy data with the raw table structure and expected results to help us clarify your structure and test to coding formula.

How to Get Your Question Answered Quickly 
BTW, I can't access the share link you posted, can you please fix this?
Regards,

Xiaoxin Sheng