How to connect fact tables that have different granularity? Please answer for both questions

[pbiforum123]

Question: 1

Cust(Customer Dimension Table)

ID

Product ID(Product Dimension Table)

ID

Employee ID(Employee Dimension Table)

ID

Sales FACT(Sales FACT table)

Cust ID

Product ID

Employee ID

Year

Month

Budget FACT(Budget FACT table)

Cust ID

Product ID

Year

How we can do the modelling with this fact table and dimension table to make the modelling work. Since Budget FACT dont have Employee ID and if you see Year Month is there in Sales FACT but only Year is there in Budget FACT. How we can model with this type of data structure?

Question: 2

Once of the FACT table contains detailed daily data and other FACT contains Monthly data. How do we connect these 2 FACT tables.

[kpost]

If 'month' is a number 1 through 12, you can do this:

add a column to that table with the End of the Month, this will make it a DATE.  that's step 1.

Month_Ending_Column = EOMONTH(DATE([Year], [Month], 1)

If the [Month] column is text like 'January', 'February', 'March' etc... Then add a column to get the Month Number First using a switch statement:

Month_Number = SWITCH(
    TRUE(),
    [Month] = "January", 1,
    [Month] = "February", 2,
   etc....
)

And then your "End of Month" column would be like this instead:

EOMONTH(DATE([Year], [Month_Number], 1)

Then create a Date Table like this:

Date_Table = CALENDAR(DATE(1990,01,01), DATE(2050,01,01))  (Use whatever range you need for your data)

And create an active relationship between this date table and your End_Of_Month column in the table that has Month & Year data, as well as a relationship between your date table and the [Date] column in the one with granularity of single day.

Now you can use columns from the Date_Table to filter your data, use as a slicer, use as the x-axis or legend in visuals where you want to combine data from both sources, etc etc.

//Mediocre Power BI Advice, but it's free//

[pbiforum123]

@kpost Thanks for replying. Any update on the first question?

[kpost]

Also I apologize because my first answer for question 2) was based on the list of fields from the first question.

You may just be able to create a Date Table and connect the Month and Day columns between the two tables and not worry about creating the Date column manually as I described.

[pbiforum123]

@kpost I did not understand. Can you please elaborate?

[kpost]

When I wrote my initial response I was under the assumption that one of the tables had a [month] and a [year] field because those were listed as fields in the big long list of fields. You have since edited that list of fields into different tables.

[pbiforum123]

@kpost Sorry for the confusion but I did not edited the fields to a different tables. 

Anyways can i assume that the answer that you gave is for question:1? Especially I would like to know the answer for below statement.

"How we can do the modelling with this fact table and dimension table to make the modelling work. Since Budget FACT dont have Employee ID and if you see Year Month is there in Sales FACT but only Year is there in Budget FACT. How we can model with this type of data structure?"

[kpost]

I don't understand the first question, it just looks like a list of fields.  What is the question, exactly?

[pbiforum123]

@kpost Sorry for that I have updated the post now...