Can't connect 3 table

This is the error message i'm getting 

This usually happens because you’re trying to force a fact-to-fact relationship, which Power BI doesn’t handle well.

The clean solution is to use the Calendar table as the single bridge:

• Make sure your Sales (daily) table is related to Calendar by Date.

• For the last year weekly table, don’t try to relate it directly to Sales. Instead, add a Week key (Year-Week) to both the weekly table and the Calendar table, then relate Weekly → Calendar on that key.

• From there, all filtering flows through the Calendar, not between fact tables.

If the grains don’t match (daily vs weekly), keep them separate and handle comparisons in measures (e.g. TREATAS or DATEADD logic), not with physical relationships.

Hi abdo132 

You have

Sales Data → daily grain (Date)

Calendar table → daily grain (Date)

Last Year (LY) data → weekly grain (Week based, not Date based)

Power BI relationships require the same granularity.

Right now you are trying to connect

Daily ↔ Weekly ↔ Daily

That’s why

Calendar ↔ Sales works

Calendar ↔ LY works (via your key)

Sales ↔ LY fails

What will not work?

Direct relationship between Sales (daily) and LY (weekly)

Multi-column composite keys across fact tables

Bi-directional relationships to “force” it

Power BI is protecting from incorrect aggregations.

Approach

Star Schema with Calendar as the bridge

Your Calendar table must be only bridge between all facts.

Calendar

   |

   | (Date)

   |

Sales (Daily)

Calendar

   |

   | (Week Key)

   |

LY Data (Weekly)

No direct relationship between Sales and LY

 1️⃣ Calendar table (must have both Date & Week fields)

Add these columns to your Calendar

YearWeek = YEAR([Date]) * 100 + WEEKNUM([Date], 2)

Optional

WeekStartDate =

[Date] - WEEKDAY([Date], 2) + 1

2️⃣ Prepare Last Year table

Your LY table should also have YearWeek

YearWeek = [Year] * 100 + [WeekNumber]

This creates a common weekly key.

3️⃣ Relationship

| From | To | Cardinality | Direction |

| ------------------ | ------------ | ----------- | --------- |

| Calendar[Date] | Sales[Date] | 1 → * | Single |

| Calendar[YearWeek] | LY[YearWeek] | 1 → * | Single |

Do not connect Sales ↔ LY directly

4️⃣ How to compare Sales vs LY in visuals

You do not use relationships for this.

You use Measures.

Example

Current Sales

Total Sales = SUM(Sales[SalesAmount])

```

Last Year Sales (Weekly data)

LY Sales =

CALCULATE(

    SUM('LY Table'[SalesAmount]),

    TREATAS(

        VALUES(Calendar[YearWeek]),

        'LY Table'[YearWeek]

    )

)

Now both measures respond to:

Date slicers

Month / Year filters

Visual context

abdo132 

Use a single Date table and relate both files to it – don’t join the fact tables.

DimDate =
ADDCOLUMNS (
CALENDAR ( MIN ( Sales[Date] ), MAX ( Sales[Date] ) ),
"Year", YEAR ( [Date] ),
"WeekNum", WEEKNUM ( [Date], 2 )
)

Add same Year + WeekNum (or a YearWeek key) to your LY weekly file.

Relationships:

DimDate[Date] → Sales[Date]

DimDate[YearWeek] → LY_Weekly[YearWeek]

Use DimDate in slicers. Both daily Sales and weekly LY data will filter correctly through the shared date table.

If this answer helped, please click Kudos or Accept as Solution.
-Kedar
LinkedIn: https://www.linkedin.com/in/kedar-pande

abdo132 

Usually we connect those tables by date column. If you don't have the date column, you can change week column to date. 

then you can connect two tables by date column

Comparing weeks across years is generally futile. Best you can do is DATEADD(...,-364,DAY)

Hi abdo132 

What's stopping you from combining the current year and last year's data into a single table? That should simplify calculations.

Hi @abdo132 ,
Since we didnt hear back from you, we would be closing this thread. If you have any further queries, you can open a separate post with sample data so that we can assist you better.

Thank You for reaching out on Microsoft Community Forum.