Do I need another measure to calculate how the contract revenue is recognised over time.
I’d like to do this on a daily basis and also, Demo Model.pbix (181.0 KB) by a straightline monthly basis ie doesn’t matter how many days in the month - if the contract has a date in that particular month then 1mths value would be taken
The problem I have as per original question is that the formula doesn’t take into account the number of days in the month where a contract is valid
I’'ve tried the following:
Contracts =
VAR dayrate = [Contract Revenue in Progress]
VAR days = CALCULATE( COUNTROWS( 'Event Dates'),
FILTER( VALUES(Contracts[Date On]), Contracts[Date On] <= MAX('Event Dates'[Date])),
FILTER( VALUES( Contracts[Date Off]), Contracts[Date Off] >=MIN('Event Dates'[Date])))
return
dayrate*days
Nov should equal 16 days x £1.64 as the only contract valid in Nov was from 15th Nov - 30th Nov
but this gives me the sum of the day rate * # days in the month rather than the sum of the day rate only taking into account when the contract is valid
Dec s/be 31days x 1.64
Jan s/be sum of 31 days x £.164 + 26days x £27.40 = £763.24
How can I get the formula to take into consideration the On Off dates for each contract when looking by Month and Year
The only contract that is “active” in November is Contract No. A2 but it didn’t start (Date On) until 15th of the month ie 16 days being 15th - 30th Nov inclusive. In Dec the same contract would be valid for the whole of the month ie 31 days. In Jan 18 it should be Contract A2 for 31 days + Contract A3 for 26 days as Contract A3 doesn’t start until 6th Jan.Demo Model 2.pbix (431.6 KB)
It is possible to do in DAX, but I think it would get pretty complicated pretty fast since you would have to do what you would in power query, but in DAX. Meaning, in your example, that contract would need somehow to be put in a row for December, January…all the way to the contract end date. DAX would use some sort of mix of generate or generateseries, crossjoin, and who knows what else. And the end result is the same as power query; get a row for each date between the date on and date off columns.
While dax could do this I would just push to the power query for a few reasons:
Much much easier. PQ is a data shaping (ETL) tool. And this is a data shape problem.
the DAX code would be long and complex which is a recipe for slow performance and a hard time troubleshooting
A table built in power query will only be updated when the data is refreshed vs. the DAX version which would be updated at every call
DAX is not the end-all-be-all. It is simply a tool, a tool that is best used in aggregating data, not shaping it. That is why msft gave us power query In an idea world the DAX we write would be as simple as possible since that is the best for performance and troubleshooting. But that requires more time in the data modeling stage.
Nov should equal 16 days x £1.64 as the only contract valid in Nov was from 15th Nov - 30th Nov
In an idea world the DAX we write would be as simple as possible since that is the best for performance and troubleshooting. But that requires more time in the data modeling stage.