cancel
Showing results for
Did you mean:

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

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.

 CurrentQtr Prior Qtr 2021 q1 600 M1 100 M2 200 M3 300 Q2 110 600 M4 60 100 M5 50 300

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
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
//OR

Frequent Visitor

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

Anonymous
Not applicable

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

Frequent Visitor

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

Announcements

#### New forum boards available in Real-Time Intelligence.

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

#### Power BI Monthly Update - May 2024

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

#### Fabric certifications survey

Certification feedback opportunity for the community.

Top Solution Authors
Top Kudoed Authors