How do I generate a Y/N column if a product was in or out of promotion (Effective Dates)

[briguin]

I have products that go in and out of promotion throughout the year. I have 2 tables, Sales and Effective Dates.

I would create a column to flag if an item was in or out of promotion during a specific time period.

Then create a measure to do my math. My desired result is the chart below.

I manually created the far right "Was_Promoted" column , Can it be done in DAX?

Sales table:

Product	Date	Total_Sales	Total_NotSold	Was_Promoted
Prod1	01/01/21	100	25	1
Prod1	01/02/21	200	10	1
Prod1	01/03/21	100	0
Prod1	01/04/21	400	0	1
Prod1	01/05/21	500	20	1
Prod1	01/06/21	100	30	1
Prod2	01/01/21	400	50
Prod2	01/02/21	500	25
Prod2	01/03/21	200	10	1
Prod2	01/04/21	100	20	1
Prod2	01/05/21	700	30
Prod2	01/06/21	100	10

Promotion Effective Date

Product	Start_Date	End_Date
Prod1	01/01/21	01/02/21
Prod1	01/04/21	01/06/21
Prod2	01/03/21	01/04/21

[Vera_33 · Accepted solution]

Hi @briguin 

I thought you wanted a Calculated column for this manually created "Was_Promoted", a disconnected promotion table

Was_Promoted = 
VAR CurProduct = SalesTable[Product]
VAR CurDate = SalesTable[Date]
VAR T1=FILTER(PromotionTable,PromotionTable[Product]=CurProduct&&PromotionTable[Start_Date]<=CurDate&&PromotionTable[End_Date]>=CurDate)
RETURN
COUNTROWS(T1)

[parry2k]

@briguin yes that prod_dim doesn't need much except this table will have unique product values to set the relationship between promotion and sales table.

[parry2k]

@briguin it will make more sense to have a Product dimension that has a unique product and have a relationship with both the tables, one to many.

and then add this custom column in Sales Table.

Promoted = 
VAR __table = CALCULATETABLE ( Promotion )
VAR __sales = CALCULATETABLE ( Sales ) 
VAR __promotionCount = COUNTX ( CROSSJOIN ( __sales, __table ), IF ( Sales[Date] >= [Start_Date] && Sales[Date] <= [End_Date], 1 ) )
RETURN IF (  ISBLANK ( __promotionCount ), "No", "Yes" )

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡

[briguin]

@parry2k 

I think you are saying I need a 3rd table, a Prod_Dim.

What additional information would you put in a Prod_Dim table? 

Prod1 is a unique key. We sale the same Product all year we just promote it differently behind the scenes.