Need Projection for 3 next months based on some formula

[Krishna_14hcl]

Dear All,

I am very new in power BI, I am stuggling with problem where I need a logic or measure where I need 3 months projection data based on average of last months but it should be without any source data in power bi visual.

I need Average of last two month count as attrion in Oct, nov, Dec and data should be actual data should be till sept'24. Is there any way or measure to visulize this. I have givenbelow screenshot and raw data for reference and used measure as well as under.

Your help will be greatly appreciated.

**bleep** Attrition =

CALCULATE(

    SUM('Att'[Att]),

    FILTER(

        ALLSELECTED('Month'[Code]),

        ISONORAFTER('Month'[Code], MAX('Month'[Code]), DESC)

    )

)

**bleep** HC =

CALCULATE(

    SUM(HC[HC]),

    FILTER(

        ALLSELECTED('Month'[Code]),

        ISONORAFTER('Month'[Code], MAX('Month'[Code]), DESC)

    )

)

Attrition% =

DIVIDE([**bleep** Attrition]*12, [**bleep** HC])

Month	HC	Att	Remarks	Att%	Code
1-Apr-24	3338	29	Actual Data	10.43%	a-Apr
1-May-24	7549	44	Actual Data	8.05%	b-May
1-Jun-24	9611	26	Actual Data	5.80%	c-Jun
1-Jul-24	8198	29	Actual Data	5.35%	d-Jul
1-Aug-24	7562	36	Actual Data	5.43%	e-Aug
1-Sep-24	9658	48	Actual Data	5.54%	f-Sep
1-Oct-24	8610	42	Average of last previous Months	5.59%	g-Oct
1-Nov-24	9134	45	Average of last previous Months	5.64%	h-Nov
1-Dec-24	8872	44	Average of last previous Months	5.67%	i-Dec

[bhanu_gautam]

@Krishna_14hcl , First try to create a measure for

Create a measure to calculate the average attrition for the last two months of actual data dynamically.

Average_Last_Two_Months_Attrition =
VAR LastTwoMonths =
CALCULATETABLE(
TOPN(2,
SUMMARIZE('Att', 'Att'[Month], "Attrition", SUM('Att'[Att])),
'Att'[Month],
DESC
)
)
RETURN
AVERAGEX(LastTwoMonths, [Attrition])

Then Use the average calculated in step 1 to project the attrition for the next three months dynamically

Projected_Attrition =
VAR CurrentMonth = MAX('Att'[Month])
VAR LastActualMonth = CALCULATE(MAX('Att'[Month]), 'Att'[Remarks] = "Actual Data")
VAR IsProjectionMonth = CurrentMonth > LastActualMonth && CurrentMonth <= EDATE(LastActualMonth, 3)
RETURN
IF(
IsProjectionMonth,
[Average_Last_Two_Months_Attrition],
SUM('Att'[Att])
)

Then Calculate the projected attrition percentage dynamically

Projected_Attrition_Percentage =
DIVIDE(
[Projected_Attrition] * 12,
CALCULATE(
SUM('Att'[HC]),
FILTER(
ALLSELECTED('Att'),
'Att'[Month] = MAX('Att'[Month])
)
)
)

[Krishna_14hcl]

Dear Bhanu,
Thanks for replying and suggestion and I used the given measures accordingly but I am not getting the exact result. It is not showing me for next months after actual data (Oct, Nov, Dec). Please check below screenshot.
I also tried with and without source data.

[Irwan · Accepted solution]

hello @Krishna_14hcl 

please check if this accomodate your need.

i assumed you want to do this in measure since you said "without any source data"

1. create a new measure for indexing (check the last 3 month)

Index =
var _Date = SELECTEDVALUE('Table'[Month])
Return
RANKX(
    ALL('Table'),
    CALCULATE(SUM('Table'[Month])),,
    ASC,
    Dense
)

2. create a new measure for calculating Projected Att.

Projected Att = 
var _Date = SELECTEDVALUE('Table'[Month])
var _Att = SELECTEDVALUE('Table'[Att])
var _MaxIndex = MAXX(ALL('Table'),[Index])
Return
IF(
    [Index]<_MaxIndex-2,
    _Att,
    AVERAGEX(
        FILTER(
            ALL('Table'),
            [Index]<_MaxIndex-2
        ),
        'Table'[Att]
    )
)

3. create a new measure for calculating Att%

Att% = 
var _HC = SELECTEDVALUE('Table'[HC])
Return
DIVIDE([Projected Att]*12,_HC)

Hope this will help.

Thank you.