Prior quarter QTD

[prash4030]

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 available in my dataset). For Closed quarters, i'm able to calculate. i managed to write below query but it is giving wrong total at quarter. in below table, i want 300 in prior quarter Q2 but getting 600.

			CurrentQtr	Prior Qtr
2021
	q1		600
		M1	100
		M2	200
		M3	300
	Q2		110	600
		M4	60	100
		M5	50	300

VAR LastMonthInQuarterAvailable = MAX('Time'[MonthInQtr])

VAR LastYearQuarterAvailable = MAX ( 'Time'[QtrNumber])

VAR PreviousYearQuarterAvailable = LastYearQuarterAvailable

var Result =

if (ISFILTERED('Time'[Quarters]),CALCULATE (

[TotalActual], FILTER(ALL('Time'),

'Time'[MonthInQtr] <= LastMonthInQuarterAvailable &&

'Time'[QtrNumber] = PreviousYearQuarterAvailable)), BLANK())

return Result

month in qtr column has values 1,2 and 3 

Quarter Number column has unique number for each quarter. Eg for FY21, there are 4 unique values like 8012,8013,8014,8015

[amitchandak]

@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]

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

[Anonymous]

@prash4030 

The fact that you don't have continuous days is irrelevant to having a proper date table in the model. You must have a proper date table at the day level granularity. Only then will you be able to perform time-intel calculations correctly. If there are pieces of time you don't need from the table, well, you then just hide them from view. This is the first hint.

Second hint is this: Hiding future dates for calculations in DAX - SQLBI

[prash4030]

thanks for your reply. i have a custom date table (Fiscal yr stating Nov). i went thru your hint.

any suggestion on the  dax 

VAR LastMonthInQuarterAvailable = MAX('Time'[MonthInQtr])

VAR LastYearQuarterAvailable = MAX ( 'Time'[QtrNumber])

VAR PreviousYearQuarterAvailable = LastYearQuarterAvailable

var Result =

if (ISFILTERED('Time'[Quarters]),CALCULATE (

[TotalActual], FILTER(ALL('Time'),

'Time'[MonthInQtr] <= LastMonthInQuarterAvailable &&

'Time'[QtrNumber] = PreviousYearQuarterAvailable)), BLANK())

return Result