OK, as suspected, it’s your data model that’s giving you a world of problems here. Even if your DAX is flawless, it will never give you the correct results with the data model structured as is. Here are the problems that need to be corrected (mixed in with some best practices that will just make things a lot easier…):
- rename different Name fields to something more descriptive, e.g., Customer Name, Store Name, etc. Very confusing to have multiple fields called Name that refer to completely different things. Renaming them will make DAX debugging and visualization creation much easier.
- rework data model – Refit is not a fact table. It is a dimension/lookup table with a slow changing dimension (refit date). Don’t worry about the slow changing dimension (SCD) aspect for the moment - let’s get the general data model correct first, but at some point you will need to make a decision about how to handle the SCD. (You can find a discussion of the relevant strategies here on the forum)
- rename Refit table to Stores - refit date is just an attribute of Stores
- restructure Customer table – should include both Customer ID and Customer Name fields
- the relationship you have between your Customer table and your Refit table is very problematic for two reasons: a) there should be no direct relationships between lookup tables; b) the relationship you currently have is between Customer Name and Refit Date, which have no logical relationship to each other and are of different data types
- your lookup/dimension table relationships are made via the name fields, rather than unique ID fields. This is a problematic because you may have, say two different customers named Smith, which will result in a many-to-many relationship between your Customer table and your Sales table (to be avoided if at all possible, since it will substantially complicate your data model and DAX, and is easily avoidable in this case)
- your Store table should contain a unique Store ID field for the reasons discussed above in 6)
- your Sales table needs to have a field for Store ID (or at least Store Name, though that is far less optimal than unique ID number for the reasons discussed above). Without a way to create a direct relationship between those two tables, you won’t be able to accomplish the analysis you want to perform.
- you need to properly mark your Dates table as a date table. Without doing so, your time intelligence functions will not work correctly.
With these changes, your data model will now conform to the ideal star schema:
With this schema, and a properly marked date table, it should now be a relatively straightforward task to accomplish your targeted analysis using the DATEADD function:
I would also recommend taking another look at the data modeling sections of this course:
I hope this is helpful. If you have any additional issues or questions after reworking your data model, just give a shout.
- Brian