Projected outcomes with reporting quarter selected from slicer

[WBscooby]

Hi

Please can anyone offer advice:

I have a PBI file with a table of outcomes by outcome date. We report these outcomes quarterly, starting with Q1 Apr-Jun. The

base measure is:

#Outcomes = DISTINCTCOUNT(Dim_Outcomes[OutcomeID])

We also report the outcomes on a annual basis and use a very simple forecast for incomplete years - (Total outcomes/ Number of quarters * 4). In reality it will only ever be the current year that is incomplete.

The key issue I have is that we may have data for 4 quarters but are reporting on an earlier quarter e.g. at 21st March 2024, we would still be forecasting from data from Q1 to Q3 as Q4 is not complete.

Taking Outcome 37 as an example we have:
Q1 - 31
Q2 - 33
Q3 - 22
Expected result:
((31 +33 + 22)/3) * 4 = 114.7

I have tried 2 different methodologies for this:

1) #OutcomesProjected =

Var vQuarter = max(Dim_Quarters[QIndex])
Var projected = ([#Outcomes]/vQuarter)*4

Return
Round(projected,0)

2) MaxReportQtr = Max(Dim_Quarters[QIndex])

#Outcomes_Projected_ReportingQtr =
Var ReportQ = [MaxReportQtr]
Var vQuarter = if(ReportQ<4,ReportQ,max(Dim_Quarters[QIndex]))
Var projected = ([#Outcomes]/vQuarter)*4
Var Rprojected = Round(projected,0)

Return
Rprojected

Neither of these return the desired result. Both methodologies seem to return data to include the incomplete quarter data.

Please can anyone help? I have attached a sample file - my actual file is sensitive and more complex. Page 1 shows examples of the charts and measures I would like to produce. Page 2 the quarterly data.

Test 

Thank you

[Anonymous · Accepted solution]

Hi @WBscooby ,

I see that the link you provided is OneDrive, but for some reason I can't open the file from OneDrive, could you please put the pbix file in some other link or just provide the sample data and expected results in text form?

I build a sample data by myself:

My logic for determining this is that if the maximum date is not the last day of the Quarter in which the date falls, the Quarter is deemed not to have ended.
Then I use this DAX to create a measure to calculate the max QIndex:

MAX_QIndex = 
VAR _MAXDATE = MAXX(ALL(Dim_Quarters), 'Dim_Quarters'[Date])
VAR _End_of_MAXDATE = EOMONTH(_MAXDATE, 0)
VAR _QIndex_isLastDay = CALCULATE(SUM(Dim_Quarters[QIndex]), 'Dim_Quarters'[Date] = _MAXDATE)
VAR _QIndex_notLastDay = _QIndex_isLastDay - 1
RETURN
IF(
    _MAXDATE = _End_of_MAXDATE,
    _QIndex_isLastDay,
    _QIndex_notLastDay
)

And then I create another measure to calculate ([#Outcomes]/vQuarter)*4:

#Outcomes_Projected_ReportingQtr = 
VAR _A = [MAX_QIndex]
VAR _COUNT = 
CALCULATE(
    DISTINCTCOUNT(Dim_Quarters[OutcomeID]),
    FILTER(
        ALL(Dim_Quarters),
        'Dim_Quarters'[QIndex] <= _A
    )
)
VAR _B = (_COUNT / 3) * 4
RETURN
ROUND(_B, 0)

The final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.