# Calculate contract value daily

Hi
I have contract start and end dates and a contract value.
I want to calculate the value of the contracts over time.

I’ve included a column in the table for Daily Sales Value and I’m incorporating the Events in Progress pattern.

I’m stumped on how to calculate the Value based on # days within the context

This is what I have so far

``````Sales in Progress =
CALCULATE( [Daily Sales],
FILTER( VALUES( Contracts[Date Off] ), Contracts[Date Off] <= MAX('Event Dates'[Date] )),
FILTER ( VALUES( Contracts[Date On] ), Contracts[Date On]>= MIN('Event Dates'[Date])))

Daily Sales = SUM(DailyRate)
``````

I need to be able to multiply Daily Sales by # days within the context

I’m sure I’ve seen a video doing this but I can’t seem to find it.

If you need to work out the days in context in a measure then all you need is the below

COUNTROWS( Dates )

But this depends on your initial context of the calculation we you haven’t mentioned.

Need to make sure all information is included here to get the right solution.

Images or demo models will speed this up.

Thanks
Sam

Thanks

I’ve uploaded a dummy data set.

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

Thanks for all your help so far.

I quickly noticed something was off here just by testing your formula in a different context. No results show which shouldn’t be happening.

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 result.

Just a matter of following more closely the example here I think.

The formula had the date around the wrong way and also one of your operators was around the incorrect way also.

Thanks
Sam

Sorry my bad in the demo.

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

Thanks for you help so far

It’s just not clear to me where in your data you have what is considered a ‘valid day’

What’s the logic here? I’m honestly not sure by what you’ve mentioned above.

You say the Nov. has 16 days that are valid but where is that in the data?

Can you also add any updates you make to your demo file so that I can see what you have done.

This will speed up any assistance

In the Contracts table there is a Date On and a Date Off field

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)

Thanks for help

Hi
I have a solution in power query. Basically adding a line for every month contract is valid and then summing

Is what I’m trying to do possible just within DAX?

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:

1. Much much easier. PQ is a data shaping (ETL) tool. And this is a data shape problem.
2. the DAX code would be long and complex which is a recipe for slow performance and a hard time troubleshooting
3. 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.