cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
smathers Frequent Visitor
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.

PB_post.JPG

5 REPLIES 5
Super User
Super User

Re: Monthly Average but not of Sales but rather Contracts

Hi,

 

Please share the exact result that you are expecting.

smathers Frequent Visitor
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.

smathers Frequent Visitor
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
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] )
    )
)

Monthly-Average-but-not-of-Sales-but-rather-Contracts

 

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
Community Support Team

Re: Monthly Average but not of Sales but rather Contracts

Hi @smathers,

 

Could you please mark the proper answers as solutions?

 

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.