Extrapolate average montly expenses to remaining months of the year

[Anonymous]

Hi all,

I am trying to find a solution for the following problem and I do not have that much experience yet with Power BI. Therefore the help of this community would be much appreciated! 

I want to use the monthly average of expenses for current year and extrapolate that value for the remaining months of the year. 

On top of that I would like to have the possibility to multiply the extrapolated value for the months of may and nov with a value such as 1.1. 

So if available expenses are available up to february, the extrapolated outcome should look like this:

Month	Expense amount	Multiply value
jan	10
feb	6
mrt	8
apr	8
may	8.8	1.1
jun	8
jul	8
sep	8
oct	8
nov	8.4	1.05
dec	8

Thanks in advance for thinking along with me!

Best,

Yoran

[Anonymous]

Thanks for the help! 

[DataInsights · Accepted solution]

@Anonymous,

This solution uses a star schema with a date table.

Measures:

Total Amount = SUM ( FactTable[Expense Amount] )

Extrapolated Amount = 
// use ALLSELECTED to use only filters external to the visual
VAR vAverage =
    CALCULATE ( AVERAGE ( FactTable[Expense Amount] ), ALLSELECTED () )
VAR vTable =
    ADDCOLUMNS (
        VALUES ( DimDate[Month] ),
        "@Amount",
            SWITCH (
                TRUE,
                [Total Amount] <> BLANK (), [Total Amount],
                DimDate[Month] = "May", vAverage * 1.1,
                DimDate[Month] = "Nov", vAverage * 1.05,
                vAverage
            )
    )
VAR vResult =
    SUMX ( vTable, [@Amount] )
RETURN
    vResult