Calculate values between dates - unrelated tables (many-to-many)

[Anonymous]

Hi!

I have 4 tables : Sales, Product, Date, and Promotion, and I want to calculate money made between the dates in Promotion table. I've made a dummy dataset to replicate mine:

The "Value" in Sales is total money made on each transaction, excluding any promotion discount.

All tables are joined to Sales, except Promotion. I tried, but it causes a many-to-many relationship. I also tried to create an index to each transation in the Sales table and join to Promotion, but this resulted in circular dependancy.

The problem is that each promotion contains several products, so there is no unique ID:

What do you think is the best solution? It feels that joining it to Sales is the best (star schema) but it doesn't work for me. Is it possible? Or should I join Promotion to Product and Date, and use then DAX measures?

Thank you for any advice!

[Anonymous]

I was missing that last filter. Now I'm able to join Promotions to Sales - thank you!

[jcalheir]

Hi

I feel like your best solution would be to have a PromotionID in the sales table. In order to do that you will need to join both tables by Product and a date range.

How exactly have you tried to join both Sales and Promotions table? Have you tried in Power Query? Ou directly in your data source?

Kind regards,
José
Please mark this answer as the solution if it resolves your issue.
Appreciate your kudos! 🙂

[Anonymous]

Hi Jose,

Besides trying to join ProductID to ProductID (many-to-many), I created a calculated column "PromotionID" in Sales using DAX:

PromotionID = CALCULATE(

   VALUES(DimPromotion[PromotionID),

   FILTER(DimPromotion,

   'Sales'[Date] >= 'DimPromotion'[StartDate]

   && 'Sales'[Date] <= 'DimPromotion'[EndDate]

   ))

This gave me a new column in which the PromotionID appeared for each transaction within the promotion period. It's blank for any transactions not involved in the promotion. When I joined it to Promotion this resulted in circular dependancy.

I'm very new to power bi, so thanks for any help!

[jcalheir · Accepted solution]

Is it possible for you to share your pbi file so we can help you out?

Maybe try this as a calculated column:

PromotionID = CALCULATE(

   VALUES(DimPromotion[PromotionID),

   FILTER(DimPromotion,

   'Sales'[Date] >= 'DimPromotion'[StartDate]

   && 'Sales'[Date] < 'DimPromotion'[EndDate]

   && 'DimPromotion'[ProductID] = 'Sales'[ProductID]

   ))