Forum Discussion
Previous Year Prorate MTD Calculation
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.
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)
2 Replies
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)CALCULATE(Sum('fact'[Sales]),DATESMTD(dateadd('DimDate'[Date],-1,year)),'DimDate'[Date]<=_max)orTOTALMTD(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)
