@Neville,
I went a full 12 rounds with this beast, and at the end of the fight I’m still not sure who came out on top. I definitely think I have a framework that does what you need it to do, but I’m not sure if the forecast numbers calculated are correct. This is where your Excel calculation will be helpful to validate one way or the other.
OK, so here’s the explanation of the attached solution:
- created a physical table via DAX, using the construct we discussed above in this thread - the only one of many I tried that retained the blank rows, which was necessary to trigger your forecast logic:
// The NATURALLEFTOUTERJOIN/CROSSJOIN construct is used to retain the
// blank rows necessary to trigger the forecast logic
FCast Lookup =
ADDCOLUMNS(
NATURALLEFTOUTERJOIN(
CROSSJOIN(
VALUES( 'Date'[MonthYearLong] ),
VALUES( Revenue[Customer] )
),
Revenue
),
"vTotRevenue", CALCULATE(SUM( Revenue[Revenue] ))
)
- Added the forecast column to the above table. The TREATAS statement was used to create a virtual relationship between this table and the Date table, necessary for the time intelligence functions to operate properly. The ALLEXCEPT statement was used to remove the filters from MonthYearLong and Customer, in order to aggregate by the proper groupings:
// Outer CALCULATE statement is necessary for context transition
// TREATAS is necessary to create the virtual relationship with the
// Date table required for time intelligence
FCast =
CALCULATE(
IF (
SELECTEDVALUE( 'FCast Lookup'[vTotRevenue] ) = BLANK(),
CALCULATE(
AVERAGE( 'FCast Lookup'[vTotRevenue] ),
PARALLELPERIOD ( 'Date'[Date], -1, QUARTER ),
// DATEADD( 'Date'[Date], -1, QUARTER ),
ALLEXCEPT(
'FCast Lookup',
'FCast Lookup'[MonthYearLong],
'FCast Lookup'[Customer]
),
ALLEXCEPT(
'Date',
'Date'[FiscalYearQuarter]
),
TREATAS(
VALUES( 'Date'[MonthYearLong] ),
'FCast Lookup'[MonthYearLong]
)
),
BLANK ()
)
)
- Added a column for Fiscal Year Quarter to provide the proper aggregation context for the table and column chart visuals:
FYQ =
CALCULATE(
LOOKUPVALUE(
'Date'[FiscalYearQuarter],
'Date'[MonthYearLong],
SELECTEDVALUE( 'FCast Lookup'[MonthYearLong] )
)
)
- Created a measure to sum the forecast (FCast) column by FYQ, also requiring TREATAS to create the virtual relationship between the FCast and Date tables:
Total FCast by FYQ =
CALCULATE(
SUMX(
'FCast Lookup',
'FCast Lookup'[FCast]
),
ALLEXCEPT(
'Date',
'Date'[FiscalYearQuarter]
),
TREATAS(
VALUES( 'Date'[MonthYearLong] ),
'FCast Lookup'[MonthYearLong]
)
)
So, here’s what it looks like all put together:
As you can see in the screenshot above, I also created a measure version of the FCast calculated column, just to check whether it was calculating properly in context, since calculated columns can get wonky when you start adding in complex filter context changes. But in this case, both the calculated column and measure provide the exact same results line by line (except I didn’t bother to fix the grand total problem for the measure, since we know why that occurs and it’s not at all unique to this case).
Full solution file posted below. Hope this is helpful.
