Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
smathers
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
v-jiascu-msft
Employee
Employee

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.
Ashish_Mathur
Super User
Super User

Hi,

 

Please share the exact result that you are expecting.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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.

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])

 

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.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.