Can't connect 3 table

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