Forum Discussion
Question on Partial Quarters
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.
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))
1 Reply
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))