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
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
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Top Kudoed Authors
Users Online
Currently online: 170 members 1,867 guests
Please welcome our newest community members: