Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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