cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
michbake Regular Visitor
Regular Visitor

Need help with combining data in 2 fact tables - forecast and actual

DataModel.jpgData Modelgrid.jpgData table - report

 

I have the data model above with 2 fact tables.  One for Forecast and one for actuals.  They are at different granularities.  Forecasts are for the month - but done every week.  Actuals are added for the month when the month is complete.  I want a data table report as shown above actuals to replace forecast once they are available.  I am not xcertain if I need a different data-model or I need to de-normalize to a single fact table or DAX measures or DAX calculated columns.

 

 

4 REPLIES 4
Highlighted
Super User
Super User

Re: Need help with combining data in 2 fact tables - forecast and actual

This is a DAX problem, not power query. Your data model is fine. Break the problem into pieces and solve one problem at a time. 

 

1. Write a measure that returns actual

2. Write a measure that returns forecast

3. Write a measure (probably using IF ) to show one or the other depending on the rules you describe. 



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
michbake Regular Visitor
Regular Visitor

Re: Need help with combining data in 2 fact tables - forecast and actual

@MattAllington  Matt, thanks for your reply - I am a DAX beginner, and I have tried numerous approaches - I continue to struggle.  Can you propose the structure of the DAX measure?  Nothing I do seems to work.  Maybe I can send a data sample file?  Any help is appreciated!

Super User
Super User

Re: Need help with combining data in 2 fact tables - forecast and actual

I can help you learn.  My view is you wont learn if I do it for you.  How about you post a sample workbook with what you have done.  I can then guide you from there.



* Matt is a Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
michbake Regular Visitor
Regular Visitor

Re: Need help with combining data in 2 fact tables - forecast and actual

@MattAllington  Sample at URL below.

https://drive.google.com/open?id=1ATCnatMbzd14j9EBH5EWJZelJzyvSyYH

 

I am using Excel/Power Query.

I simply add the ForecastUnitsForward measure to the SalesUnits measure for the integrated measure.

 

ForecastUnits Forward=
SUMX(
 'CDC Data',
 IF(RELATED('Table_Period'[Month])>='CDC Data'[Forecast Month],
 [Forecast Units],
 [Sales Units])
)

 

I have a row total problem.  Any guidance is appreciated.

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Top Solution Authors
Top Kudoed Authors (Last 30 Days)