This topic might have been discussed and covered in the forum before, but I could not find it. I have a very simple case to calculate the sales during promotion. In my testing file, you can find 3 tables, one is product table, one is sales table and one is promotion table. The promotion table contains the product ID and the promotion start date and end date for each campaign. What I need is to calculate the sales of each products during the campaign period when I select a campaign from the filter on the top of the page. I also want to calculate the product sales 4 weeks prior to campaign start date. Could you please help to build this kind of report using DAX?
Hey, Mike! In looking at your PBIX file, this is really much more of a data modeling problem than a DAX problem, and your data model currently has two major problems:
your questions at their core are time intelligence questions, which absolutely require a dedicated date table within your data model. The best date table out there is @Melissa’s Extended Date Table, which I highly recommend everyone use, but particularly in this case since her dynamic offset fields will make your 4-weeks prior calculation a snap.
The Products to Promotion relationship (circled below) is running in the wrong direction, and I don’t like the snowflake structure of promotion filtering product. (To understand why not, please refer to the solution video and Powerpoint for Week #2 of the Power BI Accelerator course on the portal). I would restructure this data model into a star schema.
Here’s a post dealing with a very similar (though more complex) version of your sales campaign question.
Take a look through these, and take a run at restructuring your data model as recommended above. Once you’ve got that right, your DAX should fall into place quite quickly. I’ve also included a link to Melissa’s video on using the Extended Date Table offsets capabilities, which is an awesome tool to have in your DAX toolbox.
@BrianJ Hi, Brian, thanks for sharing the resources with me. I just got the chance to view the Extended date table from @Melissa and I found it very powerful, especially the offset of month and week. In the past, I have build my own using M language, which is very time consuming sometimes. With this tool, it will make my work on PowerBI much easier. I will add this to my model. I also checked the post you shared with me. It is very similar and I will update my data model based on the recommendation. The only difference I can tell is that I wanted to show a table with all products for s selected campaign. Let me try it first and see how far I can go. I will share back my new data model with you if I still have questions.
One thing to consider is de-normalizing the products table by combining it with the promotion table. Only you know all the questions you want your report to address, but for the ones you’ve laid out above, I think that might work really well.
See how the restructuring goes, and if you run into problems just give a shout.
Hi, @BrianJ , I have a challenge with my data model. In my Promotion table, one product could have multiple promotion and one promotion could have multiple products. I cannot connect Promotion table to my Sales table through field ProductID. I am struggling with this challenge, could you please share my thoughts or any suggestions?
@BrianJ After I post my question, I saw your reply. I believe the you suggest merging product table to my promotion table. Did I understand it correctly?
Since one product could have multiple campaigns, it will still end with duplicate products in the promotion table, which will cause many-to-many relationship between promotion table and sales table. How to overcome this challenge?
Here is the updated version of my data model with two tables merged. Promotion.pbix (160.7 KB)
What I am struggling with is the calculation for promotion product. I need to calculate the sales of the promotion products with the sales date between campaign start date and end date. Since the relationship between campaign start end and sales date is not active, which function I can use to do the date comparison?
Okay, I think we’re making progress here. I think the next step is working to get rid of that bidirectional relationship. I think if we write some code to bring promotion ID into the sales table based on product ID and date, what we can do then is created a composite ID representing the concatenation of promotion ID and product ID in both the promotion and sales tables, turning the bidirectional relationship into a 1:M.
That should let you determine both what products were bought under a given promotion, and what promotions were associated with a given product without a M:M or a bidirectional relationship.
OK, I’m intrigued by this one. I built out the data model above, and it doesn’t work because not every date in the Sales table is associated with a promotion. Thus, on a composite key, blank promotions will throw product into an unknown status, and you get a result that looks like this:
But the effort was not wasted, since it put Promotion ID field in the Sales table, and we can link on that for Promos, and then link the Product table and the Sales table through Product ID, leading to this data model:
This is now a fully conforming star schema model. The only downside is that to get the slicers to synch properly, you need to use the product ID and promo ID fields from the sales table rather than from the dimension tables. Works fine, just will be a bit slower on a large model.
Note: in building this out, I also found you had a major referential integrity problem with your products table – that table only contained four values for product, while there were 10 unique values for product in your sales table. I subsequently expanded the product table to cover these additional values. The model now works great, producing accurate values and synching the slicers.
Thanks, @BrianJ , sorry for the late response. I was planning to work on your suggested data model this morning. Just need to get some nice sleep.
I built my dummy date set based on some real data and some dummy data I created. I should have thought about the product table. Sorry for the confusion and I will keep that in mind for my future post.
Your solution works great and as you mentioned that the only downside is that I cannot use the Promotion table as my filter, which can give the end users more campaign details such as Campaign start date and Campaign end date. I changed the relationship between promotion and sales table, it seems working. Not sure if there is a better way to do it.
I brought the campaign start date to the sales table and try to build a DAX to calculate the prior 4 weeks sales for each product. But my formula did not work. Here is the formula:
Changing unidirectional relationships to bidirectional is not the way you want to go. I liken bidirectional relationships to nitroglycerine. If you know EXACTLY how to handle it, it’s very useful, but for the rest of us it will likely just cause a spectacular explosion. Bidirectional relationships are the same - one misplaced bidirectional relationship can undermine the integrity of your entire data model and render your DAX inaccurate, even if code-wise it’s exactly correct. The article/video below does a fantastic job explaining this.
So, here’s what I did - I reset the relationship to unidirectional, but then pulled promo end date into the sales table using the RELATED function, so that we would have access to it for the visuals you want to create without needing to rely on a bidirectional approach. You probably could also accomplish this with the CROSSFILTER command to temporarily reverse the flow of the relationship, but I think using RELATED is much simpler.
Then, once I did that, I rewrote the 4-week stepback measure as follows:
campaign sales 4 weeks prior =
VAR SelProd = SELECTEDVALUE( Sales[ProductID] )
VAR SelPromo = SELECTEDVALUE( Sales[PromotionID] )
VAR Start4WksEarlier = SELECTEDVALUE( Sales[Promo_start_date] ) - 28
VAR End4WksEarlier = SELECTEDVALUE( Sales[Promo_end_date] ) - 28
VAR Result =
CALCULATE(
[Total Sales],
FILTER(
ALL(Sales),
Sales[Day] >= Start4WksEarlier &&
Sales[Day] <= End4WksEarlier &&
Sales[ProductID] = SelProd
)
)
RETURN Result
A key point above is that the first argument in FILTER () respects the existing filter context, so to clear that out, you need to remove existing filters on the Sales table using ALL.
Hi, @BrianJ Thanks a lot and that is exactly what I was looking for.
I was hesitate to create the bi-directional relationship. Bringing the promotion end date to the sales table solved this problem perfectly and make the DAX formula to calculate the sales 4 weeks prior much easier. Thanks a lot for guiding me through the solution and sharing those valuable insights and posts with me so that I learn more than just the solution itself.
I cannot say thank-you enough to you for all the helps you did in the past.
Truly my pleasure - you’ve always got interesting problems, and do a great job framing the questions and providing the necessary data to make it easy to provide good support. Glad that got you what you needed.
The big take-away from this one is that 99.8% of the time when you might be tempted to use a bidirectional relationship, there is another way to do it using unidirectional relationships (sometimes that’s developing composite keys, de-normalizing dimension tables, using TREATAS, building a bridge table, etc.).
The only situation I’ve encountered where there simply wasn’t a good workaround is the Period Slicer configuration that eDNA Expert @JarrettM uses frequently, and that I’ve adopted in some of my reports as well.
However, this bidirectional relationship is isolated in the far corner of the data model, snowflaked off of the extended data table, where it can cause no ambiguity or other problems.
