Financial reporting_ Budget, 3+9 forecast, 6+6 forecast and 9+3 forecast against actuals in Power BI

[4thSun]

Where are all the financial reporting gurus at? I have a requirement to report on the budget, 3+9 forecast, 6+ 6 forecast, 9+3 forecast against actuals

... so at the end of the day my matrix table will look like this

Month-year, actuals forecast, variance, YTD

Jan-24 , actuals, budget, actuals - budget, budgetYTD,

Feb-24 , actuals, budget, actuals - budget, budgetYTD,
Mar-24 , actuals, budget , actuals - budget,budgetYTD,

Apr-24 , actuals, forecast(3+9), actuals - forecast(3+9) forecast(3+9)YTD,

May-24 , actuals, forecast(3+9), actuals - forecast(3+9), forecast(3+9)YTD,
Jun-24 , actuals, forecast(3+9), actuals - forecast(3+9), forecast(3+9)YTD,

Jul-24 , actuals, forecast(6+6), actuals - forecast(6+6) forecast(6+6)YTD,

Aug-24, actuals, forecast(6+6), actuals - forecast(6+6), forecast(6+6)YTD,

Sep-24, actuals, forecast(6+6), actuals - forecast(6+6) forecast(6+6)YTD,

.... same pattern for 9+9 etc

Q: My challenge is how do I make sure that Apr-24 for example returns , actuals, forecast(3+9), actuals - forecast(3+9) forecast(3+9)YTD for april and then for other months too? 

[Anees91]

Hi there, im wanting to do the 3+9, 6+6 forecast, im struggling to understand how you did it using angith response..are you able to share your data model and measures for clarification without showing any data if possible? I wpi;d appreciate it thanks

[4thSun]

Angiths' method works - for the var currentmonth - instead of using selected value function  - use max function. I believe I shared my model in the past too. You can adopt that approach. 

Do let me know if this is helpful or provide a working model and we can deep dive into it for ya. 

[Angith_Nair · Accepted solution]

Hi @4thSun 

> For each forecast, create measures like below

Budget = SUM('ForecastTable'[Budget])
Forecast_3_9 = SUM('ForecastTable'[Forecast_3_9])
Forecast_6_6 = SUM('ForecastTable'[Forecast_6_6])
Forecast_9_3 = SUM('ForecastTable'[Forecast_9_3])

> Create a measure that selects the correct forecast based on the month:

SelectedForecast = 
VAR CurrentMonth = MONTH(SELECTEDVALUE('DateTable'[Date]))
RETURN 
    SWITCH(
        TRUE(),
        CurrentMonth <= 3, [Budget],
        CurrentMonth <= 6, [Forecast_3_9],
        CurrentMonth <= 9, [Forecast_6_6],
        [Forecast_9_3]
    )

> Calculate the variance between actuals and the selected forecast:

Variance = 
    [Actuals] - [SelectedForecast]

> Create a YTD measure that adapts to the selected forecast for each month:

YTD_Forecast = 
VAR CurrentMonth = MONTH(SELECTEDVALUE('DateTable'[Date]))
RETURN
    SWITCH(
        TRUE(),
        CurrentMonth <= 3, TOTALYTD([Budget], 'DateTable'[Date]),
        CurrentMonth <= 6, TOTALYTD([Forecast_3_9], 'DateTable'[Date]),
        CurrentMonth <= 9, TOTALYTD([Forecast_6_6], 'DateTable'[Date]),
        TOTALYTD([Forecast_9_3], 'DateTable'[Date])
    )

> Add Month-Year from your DateTable as rows in the matrix.
> Add the following measures as values: [Actuals], [SelectedForecast], [Variance], and [YTD_Forecast].

[4thSun]

@Angith_Nair  - Thank you so much! This worked beautifully as expected. I have a follow-up question:  Is there a way to use selectedForecast measure in a calculation group to return YTD values? I have initially used 
YTD = CALCULATE(SELECETEDMEASURE(), DATESYTD(DateTable[date]), "31/3")

[4thSun]

@Angith_Nair  or anyone else - I want to use the above measures: actuals, selectedForecast, and variance in a calculation group to return YTD values; this formula works for YTD actuals  = CALCULATE(SELECTEDMEASURE(), DATESYTD(DateTable[date]), "31/3") but doesn't work for YTD selectedForecast (only return budget values - doesn't seem to see 3+9, 6+6 etc)  and YTD variance. 

Is this really possible in Calculation groups or this cant be done?

[4thSun]

This is looking good. I will try out these tips and get back to you ASAP. Thanks @Angith_Nair 

[danextian]

Hi @4thSun 

Are those the column names that you want in your matrix? If so and while it is not impossible, that will require a lot of DAX and a prepared table of all the possible column names.

[4thSun]

nahh, just the 4 columns