topic Re: Two Fact Tables Nightmare - Calling all Power BI Gurus in DAX Commands and Tips

Two Fact Tables Nightmare - Calling all Power BI Gurus

7ballp25 — Thu, 12 May 2022 16:30:00 GMT

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

Re: Two Fact Tables Nightmare - Calling all Power BI Gurus

amitchandak — Thu, 12 May 2022 17:12:09 GMT

7ballp25 , Create a common date table with required columns and join that with both tables

and now create a measure

Sum(Table[Hist Sales]) + Sum(Table2[projected Sales])

Use them with columns of common date table

Re: Two Fact Tables Nightmare - Calling all Power BI Gurus

7ballp25 — Thu, 12 May 2022 17:27:53 GMT

Thanks, but already tried. Again, Consider "Forecast Table" as a table that only has dates in it, and the forecast data is based on measures and what if parameters applied to Current year data. Even when i did create more columns for Forecast Table to Union with the historcal table... it wouldnt provide correct data... it would show totals in every row... not showing the distinct values of the measures in each row.

Re: Two Fact Tables Nightmare - Calling all Power BI Gurus

datawailor — Thu, 12 May 2022 17:56:01 GMT

These types of solutions are hard to decipher without seeing the data model but to build on Amitchandak's suggestion, it's likely you'll need to adjust your calculations to leverage the dates from the common date table introduced. I had a situation where I had running totals on the fact table but when I introduced a date table that will enabled me change the axis to years or months or days, had to adjust my running total metrics to work off the new date table to get things to work properly.