date column from dim date table filtering 2 fact tables on 2 different columns

[Kevin_Conseil]

Hi everyone,

Tables:
'DimDate' -> Dimension table; date column being the key reference to filter fact tables

'SalesMonthly'-> Fact table with sales numbers on a monthly level

'SalesDaily'-> Calculated fact table with sales numbers on a daily level (cross join between 'DimDate' & 'SalesMonthly')

Data model connection:

'DimDate'[Date] -> 'SalesMonthly'[year_month]: dimdate is connected to sales monthly table on date = year_month

'DimDate'[Date] -> 'Salesdaily[date]: dimdate is conneted to sales daily table on date=date

Issue:

On my daily table appeare only the sales from the first day of the month (year_month).

Troubleshoot:

When i remove the connection in the data model between 'DimDate'[Date] -> 'SalesMonthly'[year_month] then i get the correct sales. 

Any idea how to solve the issue on my daily table while keeping the connection between 'DimDate'[Date] -> 'SalesMonthly'[year_month]?

[Kevin_Conseil]

Solution:

I just created a "year_month" column in my 'DimDate' table and link it to "year_month" in the 'SalesMonthly' table.

Seems like working 🙂 

Still interesting in getting another/better proposition if any 

[amitchandak]

@Kevin_Conseil , I only have doubt if dates are there it might become Many to Many

[Kevin_Conseil]

Yes it is a many to many relationship. Do you ave any thought about possible negative side effect?

[v-alq-msft · Accepted solution]

Hi, @Kevin_Conseil 

Generally, we don't recommend relating two fact-type tables directly using many-to-many cardinality. The main reason is because the model won't provide flexibility in the ways you report visuals filter or group.

I'd like to suggest you refer the following guidance:

• Add each many-to-many related entity as a model table, ensuring it has a unique identifier (ID) column
• Add a bridging table to store associated entities
• Create one-to-many relationships between the three tables
• Configure one bi-directional relationship to allow filter propagation to continue to the fact-type tables
• When it isn't appropriate to have missing ID values, set the Is Nullable property of ID columns to FALSE—data refresh will then fail if missing values are sourced
• Hide the bridging table (unless it contains additional columns or measures required for reporting)
• Hide any ID columns that aren't suitable for reporting (for example, when IDs are surrogate keys)

For further information, you may refer Many-to-many relationship guidance .

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@Kevin_Conseil , In the monthly table you can create a month start date and join with Date of date table

Create a new column in sales Monthly -- Assume format is YYYY-MM (2020-02)

Date = year(left([Month-year],4),right([Month-year],2),1)

In case it is Jan-2020 or Jan-20

Date = "1-" & [Month-year] // You have make this a date column

[Kevin_Conseil]

Hey Amit,

the column "year_month" in my Sales Monthly table is the "start of the month" column you are describing. And I did link it to the date column in the date table. The issue is that it is also filtering out my daily table.

[AliceW]

I think I encountered something similar. Is DimDate a calculated table? If yes, you need to expand the period in there to make sure you 'catch' every Sales date in between its start and end date.

E.g. if you have Sales from 2018 to 2020, DimDate needs to start at least from 01/01/2018.

[Kevin_Conseil]

Thanks Alice for your answer, but i do have all dates of sales in my DimDate table.