Forum Discussion

jl20's avatar
jl20
Helper IV
4 years ago
Solved

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 QT...
  • amitchandak's avatar
    4 years ago

    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))