Linking forecasts to actuals using a unique identifier and finding variances

[hmustafa]

Hello everyone,

A short synopsis of what I am trying to accomplish. Currently, using excel i am able to extract salary forecasts and actuals in order to identify variances. For example, an employee has a salary forecast for five periods in a year. For each forecast, there are five to six lines of actual pay.

Using the following formulas, i've been able to identify all segments of actual data that are a match for each forecast.

First, I assign each forecast a forecast number:

=IF(P5="Forecast",ROW(A5),"")

Next, for each line of "actuals" data, i identify matching forecasts using an employee #:

{=IFERROR(INDEX($A$5:$A$9882, SMALL(IF($D5=$A$5:$D$9882,IF(AND(OR($P5="actual",$P5="actual adjustment"),CD5="Include"),IF($P$5:$P$9882="Forecast",IF($CC5=$CC$5:$CC$9882,ROW($D$5:$D$9882)-ROW($D$5)+1)))), COLUMN(A1)))," ")}    -ARRAY. I copy and paste this formula along many columns to insure all forecasts are captured. 

I then use vlookups with the forecast number to identify the # of overlapping days and then calculate the $ value.

For each forecast, I am able to calculate the applicable actuals by use of this formula (negative balances are excluded and included elsewhere):

=IFERROR(IF(P5="Forecast",(BW5/(SUMIF($CL$5:$CL$3004,$CL5,$BW$5:$BW$209))*(SUMIFS($CU:$CU,$CQ:$CQ,$A5,$CU:$CU,">0")+SUMIFS($CZ:$CZ,$CV:$CV,$A5,$CZ:$CZ,">0")+SUMIFS($DE:$DE,$DA:$DA,$A5,$DE:$DE,">0")+SUMIFS($DJ:$DJ,$DF:$DF,$A5,$DJ:$DJ,">0")+SUMIFS($DO:$DO,$DK:$DK,$A5,$DO:$DO,">0")+SUMIFS($DT:$DT,$DP:$DP,$A5,$DT:$DT,">0")+SUMIFS($DY:$DY,$DU:$DU,$A5,$DY:$DY,">0")+SUMIFS($ED:$ED,$DZ:$DZ,$A5,$ED:$ED,">0")+SUMIFS($EI:$EI,$EE:$EE,$A5,$EI:$EI,">0")+SUMIFS($EN:$EN,$EJ:$EJ,$A5,$EN:$EN,">0")+SUMIFS($ES:$ES,$EO:$EO,$A5,$ES:$ES,">0")+SUMIFS($EX:$EX,$ET:$ET,$A5,$EX:$EX,">0")+SUMIFS($FC:$FC,$EY:$EY,$A5,$FC:$FC,">0")+SUMIFS($FH:$FH,$FD:$FD,$A5,$FH:$FH,">0")+SUMIFS($FM:$FM,$FI:$FI,$A5,$FM:$FM,">0")+SUMIFS($FR:$FR,$FN:$FN,$A5,$FR:$FR,">0")+SUMIFS($FW:$FW,$FS:$FS,$A5,$FW:$FW,">0")+SUMIFS($GB:$GB,$FX:$FX,$A5,$GB:$GB,">0")+SUMIFS($GG:$GG,$GC:$GC,$A5,$GG:$GG,">0")+SUMIFS($GL:$GL,$GH:$GH,$A5,$GL:$GL,">0")+SUMIFS($GQ:$GQ,$GM:$GM,$A5,$GQ:$GQ,">0")+SUMIFS($GV:$GV,$GR:$GR,$A5,$GV:$GV,">0")+SUMIFS($HA:$HA,$GW:$GW,$A5,$HA:$HA,">0")+SUMIFS($HF:$HF,$HB:$HB,$A5,$HF:$HF,">0"))),0),0)

Is there anyway to A) Simply this, and B) replicate this in Power BI?

[v-jiascu-msft]

Hi @hmustafa,

Could you please share a dummy sample? Because Power BI does this in a different way that is more dynamical. The data model structure and relationship are critical to a formula in Power BI. Please share a sample.

Best Regards!

Dale

[hmustafa]

Hi @v-jiascu-msft

The following is a sample of data:

Fiscal Year	Personnel number	Document Type	Forecast type description	Record type description	Forecast Group Description	Ent/Ded from date	Ent/Ded to date	Effective From Date	Effective To Date	Type of Pay	End Date	Gross Full Year Forecast	Net actual
2018	11111111	F3	Basic Pay	Actual	Indeterminate FT	2017-04-01	2017-04-05			Basic Pay		0.00	1500.00
2018	11111111	F3	Basic Pay	Actual	Indeterminate FT	2017-04-06	2017-04-19			Basic Pay		0.00	5000.00
2018	11111111	F3	Basic Pay	Actual	Indeterminate FT	2017-04-20	2017-05-03			Basic Pay		0.00	5000.00
2018	11111111	F3	Basic Pay	Actual	Indeterminate FT	2017-05-04	2017-05-17			Basic Pay		0.00	5000.00
2018	11111111	F3	Basic Pay	Actual	Indeterminate FT	2017-05-18	2017-05-31			Acting Pay		0.00	5000.00
2018	11111111	F3	Basic Pay	Actual	Indeterminate FT	2017-06-01	2017-06-14			Basic Pay		0.00	5000.00
2018	11111111	F3	Basic Pay	Actual	Indeterminate FT	2017-06-15	2017-06-28			Basic Pay		0.00	5000.00
2018	11111111	F3	Basic Pay	Actual	Indeterminate FT	2017-06-29	2017-07-12			Basic Pay		0.00	5000.00
2018	11111111	F3	Basic Pay	Actual	Indeterminate FT	2017-07-13	2017-07-26			Basic Pay		0.00	5000.00
2018	11111111	F3	Basic Pay	Actual	Indeterminate FT	2017-07-27	2017-08-09			Basic Pay		0.00	5000.00
2018	11111111	F3	Basic Pay	Actual	Indeterminate FT	2017-08-10	2017-08-23			Basic Pay		0.00	5000.00
2018	11111111	F3	Basic Pay	Actual	Indeterminate FT	2016-06-16	2016-06-16			Basic Pay		0.00	700.00
2018	11111111	F3	Basic Pay	Actual	Indeterminate FT	2016-06-17	2016-06-24			Basic Pay		0.00	2500.00
2018	11111111	F3	Basic Pay	Actual	Indeterminate FT	2017-08-24	2017-09-06			Basic Pay		0.00	5000.00
2018	11111111	F3	Basic Pay	Actual	Indeterminate FT	2017-09-07	2017-09-20			Basic Pay		0.00	5000.00
2018	11111111	F3	Basic Pay	Actual	Indeterminate FT	2016-04-01	2016-05-08			Basic Pay		0.00	1500.00
2018	11111111	F3	Basic Pay	Actual	Indeterminate FT	2016-05-09	2016-05-11			Basic Pay		0.00	300.00
2018	11111111	F3	Basic Pay	Actual	Indeterminate FT	2016-05-12	2016-05-30			Basic Pay		0.00	750.00
2018	11111111	F3	Basic Pay	Actual	Indeterminate FT	2016-05-31	2016-06-01			Basic Pay		0.00	100.00
2018	11111111	F3	Basic Pay	Actual	Indeterminate FT	2016-06-02	2016-06-16			Basic Pay		0.00	500.00
2018	11111111	F3	Basic Pay	Actual	Indeterminate FT	2016-06-17	2016-06-24			Basic Pay		0.00	300.00
2018	11111111	F3	Basic Pay	Actual	Indeterminate FT	2016-06-25	2016-08-04			Basic Pay		0.00	1500.00
2018	11111111	F3	Basic Pay	Actual	Indeterminate FT	2016-08-05	2016-08-19			Basic Pay		0.00	500.00
2018	11111111	F3	Basic Pay	Actual	Indeterminate FT	2016-08-20	2016-10-03			Basic Pay		0.00	1500.00
2018	11111111	F3	Basic Pay	Actual	Indeterminate FT	2016-10-04	2016-10-14			Basic Pay		0.00	500.00
2018	11111111	F3	Basic Pay	Actual	Indeterminate FT	2016-10-15	2016-11-07			Basic Pay		0.00	750.00
2018	11111111	F3	Basic Pay	Actual	Indeterminate FT	2016-11-08	2016-11-09			Basic Pay		0.00	100.00
2018	11111111	F3	Basic Pay	Actual	Indeterminate FT	2016-11-10	2017-01-26			Basic Pay		0.00	2800.00
2018	11111111	F3	Basic Pay	Actual	Indeterminate FT	2017-01-27	2017-02-03			Basic Pay		0.00	300.00
2018	11111111	F3	Basic Pay	Actual	Indeterminate FT	2017-02-04	2017-03-31			Basic Pay		0.00	2000.00
2018	11111111	F3	Basic Pay	Actual	Indeterminate FT	2017-09-21	2017-10-04			Basic Pay		0.00	4500.00
2018	11111111	F3	Basic Pay	Actual	Indeterminate FT	2017-10-05	2017-10-18			Basic Pay		0.00	4500.00
2018	11111111	F3	Allowance	Actual	Bilingual Bonus	2017-04-01	2017-04-05			Bilingual Bonus		0.00	15.00
2018	11111111	F3	Allowance	Actual	Bilingual Bonus	2017-04-06	2017-04-19			Bilingual Bonus		0.00	40.00
2018	11111111	F3	Allowance	Actual	Bilingual Bonus	2017-04-20	2017-05-03			Bilingual Bonus		0.00	40.00
2018	11111111	F3	Allowance	Actual	Bilingual Bonus	2017-05-04	2017-05-17			Bilingual Bonus		0.00	40.00
2018	11111111	F3	Allowance	Actual	Bilingual Bonus	2017-05-18	2017-05-31			Bilingual Bonus		0.00	40.00
2018	11111111	F3	Allowance	Actual	Bilingual Bonus	2017-06-01	2017-06-14			Bilingual Bonus		0.00	40.00
2018	11111111	F3	Allowance	Actual	Bilingual Bonus	2017-06-15	2017-06-28			Bilingual Bonus		0.00	40.00
2018	11111111	F3	Allowance	Actual	Bilingual Bonus	2017-06-29	2017-07-12			Bilingual Bonus		0.00	40.00
2018	11111111	F3	Allowance	Actual	Bilingual Bonus	2017-07-13	2017-07-26			Bilingual Bonus		0.00	40.00
2018	11111111	F3	Allowance	Actual	Bilingual Bonus	2017-07-27	2017-08-09			Bilingual Bonus		0.00	40.00
2018	11111111	F3	Allowance	Actual	Bilingual Bonus	2017-08-10	2017-08-23			Bilingual Bonus		0.00	40.00
2018	11111111	F3	Allowance	Actual	Bilingual Bonus	2017-08-24	2017-09-06			Bilingual Bonus		0.00	40.00
2018	11111111	F3	Allowance	Actual	Bilingual Bonus	2017-09-07	2017-09-20			Bilingual Bonus		0.00	40.00
2018	11111111	F3	Allowance	Actual	Bilingual Bonus	2017-09-21	2017-10-04			Bilingual Bonus		0.00	40.00
2018	11111111	F3	Allowance	Actual	Bilingual Bonus	2017-10-05	2017-10-18			Bilingual Bonus		0.00	40.00
2018	11111111		Basic Pay	Forecast	Indeterminate FT			2017-06-01	9999-12-31		9999-03-31	84516.00	0.00
2018	11111111		Acting Pay	Forecast	Indeterminate FT			2017-05-18	2017-05-31			7500.00
2018	11111111		Basic Pay	Forecast	Indeterminate FT			2016-01-27	2017-05-17			32150.00	0.00
2018	11111111		Allowance	Forecast	Bilingual Bonus			2017-02-04	9999-12-31		9999-03-31	1200.00	0.00

I match the "forecast type description" with "Type of Pay" and match "Ent/Ded from Date" and "Ent/ded to date" with the "effective from date" amd "effective to date" to capture pays that fall into that forecasted period.

Thanks,

Mustafa

[v-jiascu-msft]

Hi Mustafa,

Try this formula as a calculated column.

Sum actual =
IF (
    [Record type description] = "Actual",
    0,
    CALCULATE (
        SUM ( Table1[Net actual] ),
        FILTER (
            'Table1',
            'Table1'[Type of Pay] = EARLIER ( 'Table1'[Forecast type description] )
                && 'Table1'[Ent/Ded from date] >= EARLIER ( Table1[Effective From Date] )
                && 'Table1'[Ent/Ded to date] <= EARLIER ( 'Table1'[Effective To Date] )
        )
    )
)

Best Regards!

Dale

[hmustafa]

@v-jiascu-msft

Hi Dale,

This is great but I run into an issue where there are some actual lines which dont entirely fit into the forecast period. In scenarios where ent/ded from date is greater than the effective from date and ent/ded to date is greater than effective to date, i need the program to calculate the # of days overlapping and apply that portion to the forecast line. Does this make sense?

The way i have been doing it is calculating the number of network days between the actual period (ent/ded from date to ent/ded to date) and then finding a per day value. Then I would apply this to the forecast period by finding the number of days overlapping, either effective from to ent/ded to date or ent/ded from to effective to date.

On a side note, happy to now have a better understanding of the filter and earlier functions.

Thanks,

Mustafa