Forum Discussion

WilliamKMS's avatar
WilliamKMS
Frequent Visitor
4 years ago
Solved

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, ...
Need Help
Tips and Tricks
Tutorial Requests
  • amitchandak's avatar
    amitchandak
    4 years ago

    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)

amitchandak's avatar
amitchandak
Super User
4 years ago

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's avatar
WilliamKMS
Frequent Visitor
4 years ago
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)