Forum Discussion

prash4030's avatar
prash4030
Frequent Visitor
5 years ago

Prior quarter QTD

hello i'm struggling to calculate QoQ. when my current quarter has only 2 months completed, so i want to compare with prior 2 months only. lowest level of date is month. (no continous date availabl...
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

prash4030 , Try with date table and time intelligence

 

 

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

 

LQTD QTY forced=
var _max = date(year(today())-,month(today())-3,day(today()))
return
if(max('Date'[Date])<=_max, CALCULATE(Sum('order'[Qty]),DATESQTD(dateadd('Date'[Date],-1,year)),'Date'[Date]<=_max), blank())
//OR
//CALCULATE(Sum('order'[Qty]),DATESQTD(dateadd('Date'[Date],-1,Quarter)),'Date'[Date]<=_max)
//TOTALQTD(Sum('order'[Qty]),dateadd('Date'[Date],-1,Quarter),'Date'[Date]<=_max)

prash4030's avatar
prash4030
Frequent Visitor
5 years ago

hi, thanks for replying. i am actually having custom date table. and lowest level of fact data is month.