Break your DAX Problem into Manageable Parts
One of the concepts I discuss in my new book Learn to Write DAX, is that you should break your DAX problems into manageable pieces so that you can solve the problem one step at a time. Most of us do this intuitively in regular Excel, and it should be no different for Power Pivot and DAX. The trick is to create test measures that are used simply to solve one part of the problem. Once you have worked out each piece of the problem independently, then you can use the learnings from the test measures to create the final working solution. This blog post is more about that process of breaking the problem into pieces rather than the specific solution proposed. This solution may not be the best solution, certainly not the only solution, and probably not the most performant solution. I am thinking there may be a DAX Query solution using GENERATE and maybe CALCULATETABLE (after watching an Alberto Ferrari video last night), but in this post I just want to focus on how to break the problem into manageable pieces so you can actually get a working solution. Once you have a working solution, you may then want to find a better solution if needed (the topic for a future blog post maybe).