cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rhayami
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]
1 REPLY 1
v-tangjie-msft
Community Support
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. 

Helpful resources

Announcements
Exciting changes

Power BI Community Changes

Check out the changes to the Power BI Community announced at Build.

May 2023 update

Power BI May 2023 Update

Find out more about the May 2023 update.

Kudo Data Story carousel

Data Stories Gallery

Visit our Data Stories Gallery and give kudos to your favorite Data Stories.

Top Solution Authors