How to calculate spend in two different periods for the same client?

[AgencyPowerBi]

Hi, I have a problem that I don't know how to slove.

I have large table with companies, dates and traffic data.

I have smaller table with companies that are my clients and start-end dates when they have been my clients.

There is one-many relationship between these two tables that help me calculate traffic for specific companies in the period when they have been my clients.

PROBLEM: The same companie has become the client again.

If I enter row with new client period, I'll have many-many relationship in one-many.

How do I resolve this and avoid summarization problems?

Thanks!

[DataInsights]

@AgencyPowerBi,

You could create a bridge table that has one row per Company. This bridge table would have a one-to-many relationship with the other two tables.

[AgencyPowerBi]

If I understand correctly, duplicates go to another table? that has the same layout as initial clents table, and connects to other two tables?

[DataInsights · Accepted solution]

@AgencyPowerBi,

The two existing tables remain as they are, and you add a third table Company to your data model. The Company table is a distinct list of companies. Example: