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...
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
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