cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

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

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]
Community Support

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors