Great – really glad to hear that was helpful to you. You might have noticed that the solution file I sent to you was labeled “one fact table”. I debated sending you a different solution with two fact tables - one pivoted and one unpivoted. Both are totally workable approaches, each with advantages and disadvantages – the former makes your data model simpler and your DAX more complicated, the latter simplifies your DAX at the expense of a more complex data model. BTW - very smart to group your measures in the way you described to keep straight which fact table you’re operating on.
Getting comfortable creating and using date tables is one of the most important foundational skills in Power BI. Creating a good date table and incorporating it into my data model is literally the first thing I do on every one of my reports after importing my data. Thankfully, @sam.mckay has written the M code to make creating a really detailed date table incredibly simple. I’ve attached the txt file below, and actually use it so frequently that I keep it on my desktop so that I can always find it. I’ve also attached a brief cheat sheet I developed as a reminder to myself of what all the different fields in his date table are.
The first video below explains how to use this file, and then the other links provide some additional important background.
I hope this is helpful. Please just give a shout if you have any additional questions.
- Brian
Power BI Date Table code.txt (2.8 KB)
