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 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.
 
     
   CurrentQtrPrior Qtr
2021    
 q1 600 
  M1100 
  M2200 
  M3300 
 Q2 110600
  M460100
  M550300
 
 
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

 

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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's avatar
      prash4030
      Frequent Visitor
      5 years ago

      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
  • 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.