Question on Partial Quarters

[jl20]

Hi,

I have what I think should be a fairly simple problem to solve. I have a date table which includes a fiscal quarter dimension. I am trying to calculate my measure that compares the current QTD information with prior year information over a similar timeframe.

For example, if I run the report as of 6/1/22, it returns data for 4/1/22-6/1/22 for the current quarter, which is fine. However, when I compare to Q2 in prior years, it's returning the full quarter (i.e., 4/1/21-6/30/21, 4/1/20-6/30/20). Is there a way to wrap my measure in a calculate that will restrict the prior years so they're apples to apples (i.e., 4/1/21-6/1/21, 4/1/20-6/1/20)?

Appreciate any insight.

[amitchandak · Accepted solution]

@jl20 , Try datesqtd

Last year same QTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESQTD(dateadd('Date'[Date],-1,Year)))

or force using code like this .

comments are alternates

LQTD QTY forced=
var _max1 = maxx('order',[Order date]) // or //maxx(allselected('Date'), 'Date'[Date]) // or // Today()
var _max = date(year(_max1),month(_max1)-3,day(_max1))
return
if(max('Date'[Date])<=_max, CALCULATE(Sum('order'[Qty]),DATESQTD(dateadd('Date'[Date],-1,Quarter)),'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)

or

QTD =
var _max = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _min = eomonth(_max,-1* if( mod(Month(_max),3) =0,3,mod(Month(_max),3)))+1
return
CALCULATE([net] ,Filter('Date','Date'[Date]>= _min && 'Date'[Date] <= _max))

or

Last year QTD =
var _max1 = if(isfiltered('Date'),MAX( 'Date'[Date]) , today())
var _max = date(Year(_max) -1, month(_max) , Day(_max))
var _min = eomonth(_max,-1* if( mod(Month(_max),3) =0,3,mod(Month(_max),3)))+1
return
CALCULATE([net] ,Filter('Date','Date'[Date]>= _min && 'Date'[Date] <= _max))