cancel
Showing results for
Did you mean:
Frequent Visitor

## Monthly Average but not of Sales but rather Contracts

Hi there

I am having trouble getting the results I need. I have been asked to calculate the average per month of contract values. This would be easy in a data warehouse, but but I am having trouble when it comes to live data. I have seen monthly average examples that have helped somewhat but the focus is on sales. When it comes to contracts I need to work out what has come in for the month, what is still in and what has gone out that month, in terms of \$ and count. Not the same as avg WIP per month on projects as \$ amounts dont change on client contracts.

The data I am working with looks similar to this.... Where the avg is \$10k for jan, \$15k for feb, \$30k for Apr, etc Any recommendations from those who worked with contracts in PBI would be much appreciated.

5 REPLIES 5
Super User

## Re: Monthly Average but not of Sales but rather Contracts

Hi,

Please share the exact result that you are expecting.

Frequent Visitor

## Re: Monthly Average but not of Sales but rather Contracts

Hi Ashish, I mentioned the average figures in my post in relation to the dummy data I had displayed. Avg = \$10k for 2018-01, \$15k for 2018-02, \$30k for 2018-04.

So for instance using the data on the screen-shot for Period 2018-04  (actually it is not ideal example as I wanted to show that the contracts taken out in Januray were no longer active)

The average value of active contracts = the 3 in Feb + 1 in March + 3 April  / (3+1+3)

or another way of looking at it (value of all contracts = \$240k) - (value of expired contract = \$30k) / (count of contacts (10)-count of expired contracts (3).

Its easy to write it as above but trickier to do so its dynamically calculated for each month.  I figure I am going to have to use the EARLIER function to be able to sum or count at a monthly level.

Frequent Visitor

## Re: Monthly Average but not of Sales but rather Contracts

I initially tried to do it all in one calculation, but I worked it out in the end by splitting up each component into its own variable (though I need to confirm the figures but they look good)

Sum of Contracts Started for the month + existing  (ie sum_total_in as shown below)

Sum of Contracts Ended for the month + prior

Count of contracts started up the month

Count of contracts ended up the month

Period Start was a calculated field that gave me the last day of the month.

VAR sum_total_in =
CALCULATE(
SUM('Service Agreements History'[Coy Allocation]),
FILTER(
'Service Agreements History',
'Service Agreements History'[Contract Start Date] <= EARLIER('Service Agreements History'[Period Start])

Community Support Team

## Re: Monthly Average but not of Sales but rather Contracts

Hi @smathers,

Please check out the demo in the attachment.

1. Create a measure.

```Measure =
CALCULATE (
AVERAGE ( Table1[CONTRACT VALUE] ),
FILTER (
ALL ( Table1 ),
'Table1'[END_DATE] > MIN ( 'Table1'[START_DATE] )
&& 'Table1'[START_DATE] <= MIN ( 'Table1'[START_DATE] )
)
)
```

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Community Support Team

Hi @smathers,