PowerBI Measure Calculation:

[vsslasd1]

I have a Measure defined as follows:

HoursAlloc =

CALCULATE (

SUM ( 'CRM Opportunities'[Hours] )/ 

,

FILTER (

'CRM Opportunities',

'CRM Opportunities'[Estimated_Start_Date]

<= MAX ( 'CRM Opportunities'[Estimated_Start_Date] )

&& 'CRM Opportunities'[Estimated_End_Date]

>= MAX ( 'date table'[Date] )

)

)

Which works fine, but I want to take this value and divide by the number of months between Estimated_Start_Date and Estimated_End_Date noted above. 

While I am familiar with the DateDiff(

'CRM Opportunities'[Estimated_Start_Date], 

'CRM Opportunities'[Estimated_Start_Date], Month) but not certain how to integrate that into the measure formula above. 

Thank you

[themistoklis · Accepted solution]

@vsslasd1 

Try the following measure:

HoursAlloc =
var _timeperiod = DATEDIFF(MIN( 'CRM Opportunities'[Estimated_Start_Date] ), MAX( 'CRM Opportunities'[Estimated_End_Date]), MONTH )
var _main_query = CALCULATE (
SUM ( 'CRM Opportunities'[Hours] )/ 
,
FILTER (
'CRM Opportunities',
'CRM Opportunities'[Estimated_Start_Date]
<= MAX ( 'CRM Opportunities'[Estimated_Start_Date] )
&& 'CRM Opportunities'[Estimated_End_Date]
>= MAX ( 'date table'[Date] )
)
)
return
_main_query / _timeperiod