Forum Discussion
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 availabl...
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