Forum Discussion

7ballp25's avatar
7ballp25
Regular Visitor
4 years ago

Two Fact Tables Nightmare

I'll be refreshing this page every minute in hopes that a Power BI Guru can save my project. 

 

I was given a Historical-Current Fact Table, we will call this TABLE 1, where they wanted a plethera of measures that analyzed trend in multiple areas. Example would be like Total Sales - Easy to do. 

 

I was then asked to make a Forecast Table out of Dax, we will call this TABLE 2. This table had only had one column - Years. Under this column, were 3 rows "2023, 2024,2025". Easy.

 

With this Forecast Table, they asked for me to make measures that forecasted what the future trend would be based applying on what paramters on the Current Year.  This was doable.  I was able to apply CAGR based on the Current data provided in TABLE 1.

 

Each table has it's own measures for things like, total sales, total product, ect. Now, I'm being asked to Create a 3rd Table, a Date Table, for the specific use of displaying Table 1 and Table 2 data together. Pictures below. 

 

Now, I tried Using UNION to combine the 2 Fact tables, not working. Can someone please help me? PLEASE NOTE.. TABLE 1 "HIST SALES" IS A MEASURE, AND TABLE 2 PROJECTED SALES IS A MEASURE...I'M HOPING THAT IT'S POSSIBLE TO ACHIEVE TABLE 3 TO SHOW RUNNING TOTALS... 

 

How do I set up the relationships to achieve this?

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    O you have the same number of columns in each fact tables ? Because with Union it is a must have

    • 7ballp25's avatar
      7ballp25
      Regular Visitor

      Yes I created extra columns in the forecast table to achieve the union. However, when it comes to combining historical measures with forecasted measures, it produced totals for each row - like the picture showing 170 for each row.