Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
prash4030
Frequent Visitor

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 4
amitchandak
Super User
Super User

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

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

 

 
 
Anonymous
Not applicable

@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

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

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.