Ok first just make sure you have a handle on how you model is setup.
I looked at the example file and there were table all over the place. You need to make sure this is setup correctly and your understanding what is happening here.
I’ve just done some small re-arranging before I go any further.
Whether this is the right setup, I’m not totally sure, but this is my starting point.
Also I’m just not sure why you feel you need to create this extensive measure table?
What is the point of it? Why can’t you just use time intelligence function and DAX to sort out all these calculations? Maybe over thinking this a little bit? I’m just guessing here, but I never have to do anything like this in any work I do.
To me, no columns in here have changed at all. It’s still all time and date
This is the table you should be doing all the work to, and doing it in the query editor.
I can’t do anything myself there unfortunately.
Overall I’m just a bit confused and some of these formulas error out, so I really don’t even know what I’m looking at.
You shouldn’t be doing anything like this anyway in a fact table. All of these calculations (if they are needed at all) should be done in DAX measures.
There’s just a whole lot of simplification that needs to be done here. It is my opinion that this has been made way more complicated than it needs to be and I’m a bit lost on it myself because of this.
As a next step can you sort out the actual fact table as advised and then we can go from there.
For an example of how to calculate time intelligence on weeks, see below