@Mike ,
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.
I hope this is helpful.
- Brian
