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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

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)

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

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
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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