Previous Year Prorate MTD Calculation

[WilliamKMS]

Dear all,
Hope you guys have a great day.

I have some issues trying to make a prorate previousYear MTD calculation,

in my case the fact data is comprised of sales amount and date of the sales,
and there is calculated date dimension which contain calendar information from beginning of 2020 - to the end of 2021

the list of fact data is coming from 2020-2021, latest sales data is on 9th-Nov-2021.

What i need to do is to make a table to display the following information:

• Month number
• Current year(2021) MTD sales amount
• Previous year(2020) prorate MTD sales amount

the idea is to display previous year MTD (2020) sales amount only until 9th-Nov-2020 in the table and not the whole year sales.

I've created three measures trying to achieve that

1. Using TOTALMTD to calculate current year MTD

Sales MTD = 
CALCULATE(TOTALMTD(SUM('fact'[Sales]),DimDate[Date]), FILTER(DimDate,DimDate[Date]<=TODAY()))

and below measures is to calculate the previous year prorate MTD

2. Using SAMEPERIODLASTYEAR

Sales SamePeriodLastYear = 
CALCULATE([Sales MTD], SAMEPERIODLASTYEAR(DimDate[Date]))

3. Using combination of EDATE and CALCULATE

Sales MTD PrevYear = 
var PrevYearPeriod = EDATE(NOW(),-12)

var salesPrevYear =
CALCULATE([Sales MTD], DimDate, 'DimDate'[Date] <= PrevYearPeriod)

return salesPrevYear

and here is the results:

from the table & slicer configuration (no year selected) above, SAMEPERIODLASTYEAR measure is showing current year MTD instead of previous year MTD

and EDATE & CALCULATE measure is showing correct previous year prorate MTD

however if I select the current year (2021) slicer, the SAMEPERIODLASTYEAR measure is showing the correct previous year MTD figures but it is for the whole year instead of prorate (only calculate until 9th-Nov-2020), and EDATE & CALCULATE measure is showing blanks.

you guys have suggestions and ideas to handle this?
here is the PBIX file and the dataset if you guys want to take a closer look,
and thank you very much for your support.

[amitchandak · Accepted solution]

@WilliamKMS , Please check the measure and the alternate in comments for return

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

[WilliamKMS]

CALCULATE(Sum('fact'[Sales]),DATESMTD(dateadd('DimDate'[Date],-1,year)),'DimDate'[Date]<=_max)

or

TOTALMTD(Sum('fact'[Sales]),dateadd('DimDate'[Date],-1,year),'DimDate'[Date]<=_max)

for return in measure is working great! Thank you @amitchandak.
However, somehow the table is not showing the total figure anymore with this measure (both returns).

LYMTD Sales prevYear forced = 
var _max = date(year(today())-1,month(today()),day(today()))
return
CALCULATE(Sum('fact'[Sales]),DATESMTD(dateadd('DimDate'[Date],-1,year)),'DimDate'[Date]<=_max)
//OR
//TOTALMTD(Sum('fact'[Sales]),dateadd('DimDate'[Date],-1,year),'DimDate'[Date]<=_max)