Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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)
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)
@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.
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
@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.⚡
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |