Forum Discussion

vsslasd1's avatar
vsslasd1
Helper III
5 years ago
Solved

PowerBI Measure Calculation:

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
 
Need Help
  • themistoklis's avatar
    themistoklis
    5 years ago

    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

1 Reply

  • themistoklis's avatar
    themistoklis
    Community Champion
    5 years ago

    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