Sorry for the delay on this.
Have had a look at the model and it doesn’t seem you’ve gone through and used the techniques advised above?
If you want to spread the data out between start and end dates you need to use these techniques. It’s called the ‘events in progress’ techniques.
I’ve linked it above but will do it again here.
This can be a little tricky sometimes but very doable, you just have to make sure you’re following it exactly, including the inactive relationships in the data model.
Here’s many other examples around this formula techniques
I quickly noticed something was off here just by testing your formula in a different context. No results show which shouldn’t be happening.
[image]
This formula had a number of things wrong with it.
Contract Revenue in Progress =
CALCULATE( [Total Contract £ per day],
FILTER( VALUES( Contracts[Date On] ), Contracts[Date On] <= MAX( 'Event Dates'[Date])),
FILTER( VALUES( Contracts[Date Off] ), Contracts[Date Off] >= MIN( 'Event Dates'[Date] )))
So I updated it and now get a re…
Here’s exactly what you need for this.
It’s called the ‘events in progress’ pattern.
It’s very important to get the model and relationship set up correctly for this, as well as the DAX measures.
Everything is explained in the video. Should solve it.
Chrs
Ok I think I understand a bit better.
What is required here is a derivative of this technique showcased in the ‘Solving Analytical Scenario’ course at Enterprise DNA Online.
It’s called the ‘events in progress’ pattern
You definitely want a date table, but you need to have two ‘inactive relationships’ down to the fact table.
Run through this example, and set up your model this way, then let’s work on the formula if you can’t work it out from here
Sure happy to look into this but need to know more.
There’s a lot at play here so need to understand the data within the tables, how you setup your data model including the relationships you have and also what is the current context of the calculation.
Unfortunately with DAX it’s never a simple as just reviewing the formula by itself, there always more to it.
If you want to add a demo model here with the scenario then that will speed up assistance.
Chrs
Just as a side note on this as well a…
Sam
