Measure to combine Actual and Budget to get a full year forecast

[rhayami]

Hello,

I have 12 months (Oct-22 - Sep-23) of budget loaded with Excel.  I have 2 months (Oct-22 and Nov-22) of actual financials that are from GL data pushed from Microsoft Navision.  I simply want to combine Oct-22 and Nov-22 actual plus Dec-22 to Sep-23 budget to display the full year figure.  I am able to show the monthly figures in a matrix using the below.  

Act + FCST =

VAR MAXSelectedPeriod = MIN(JacuzziFiscalCalendar[FiscalPeriodCounter])+12

VAR MINSelectedPeriod = MIN(JacuzziFiscalCalendar[FiscalPeriodCounter])-36

VAR SelectedPeriod = MIN(JacuzziFiscalCalendar[FiscalPeriodCounter])

RETURN

IF(MAX('Date For Chart'[FiscalPeriodCounter])>SelectedPeriod,

CALCULATE([Budget Amount], CROSSFILTER(JacuzziFiscalCalendar[CalendarDate],Budget[Date],None), FILTER('Date For Chart','Date For Chart'[FiscalPeriodCounter]<MAXSelectedPeriod)),

CALCULATE([PL Amount],CROSSFILTER(JacuzziFiscalCalendar[CalendarDate],GLAccountEntry[Posting Date],None), FILTER('Date For Chart','Date For Chart'[FiscalPeriodCounter]>MINSelectedPeriod)))

What I can't get it to do is show the Total of 12 months (Oct-22 and Nov-22 actual plus Dec-22 to Sep-23 budget) in the matrix to the right.  I tried the below.  It looks like it's just adding 2 months.  Can someone please help me achieve this properly?  Thank you!!  

• Created Act TOTAL measure
• Created FCST Total measure
• Created Act + FCST TOTAL measure which is adding the above 2 measures

Act TOTAL = VAR MAXSelectedPeriod = MIN(JacuzziFiscalCalendar[FiscalPeriodCounter])+12

VAR MINSelectedPeriod = MIN(JacuzziFiscalCalendar[FiscalPeriodCounter])-36

VAR SelectedPeriod = MIN(JacuzziFiscalCalendar[FiscalPeriodCounter])

RETURN

IF(MAX('Date For Chart'[FiscalPeriodCounter])>=SelectedPeriod,

CALCULATE([YTD PL Amount], CROSSFILTER(JacuzziFiscalCalendar[CalendarDate],Budget[Date],None), FILTER('Date For Chart','Date For Chart'[FiscalPeriodCounter]<MAXSelectedPeriod)))

FCST TOTAL = VAR MAXSelectedPeriod = MIN(JacuzziFiscalCalendar[FiscalPeriodCounter])+12

VAR MINSelectedPeriod = MIN(JacuzziFiscalCalendar[FiscalPeriodCounter])-36

VAR SelectedPeriod = MIN(JacuzziFiscalCalendar[FiscalPeriodCounter])

RETURN

IF(MAX('Date For Chart'[FiscalPeriodCounter])<SelectedPeriod,

CALCULATE([YTD BUD Amount],CROSSFILTER(JacuzziFiscalCalendar[CalendarDate],GLAccountEntry[Posting Date],None), FILTER('Date For Chart','Date For Chart'[FiscalPeriodCounter]>MINSelectedPeriod)))

Act + FCST TOTAL =

[Act TOTAL] + [FCST TOTAL]

[v-tangjie-msft]

Hi @rhayami ,

According to your requirements, you want to combine the actual budget for October 22 and November 22 plus December 22 to September 23, but after the operation of the DAX expression you gave above, you can't make it show the total for 12 months in the matrix on the right, right?

Purely according to the DAX expression you gave, I did not find any problems here, you may have to combine your visual field placement and table model to troubleshoot the problem, if it is convenient, you can upload your pbix file with this problem and does not contain private data for our further study.

Please check out similar posts below that may find it helpful:

Solved: Calculate full year forecast as Actuals + Forecast - Microsoft Power BI Community

Combine actuals and forecast - DAX Calculations - Enterprise DNA Forum

Showing actuals and forecasts in the same chart with Power BI - SQLBI

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution to help the other members find it more quickly.