Use a measure result as a date

[_Kevin_]

Hi

I have 2 measures as follows:

a) CurrentMonth = MAX((Info[Reporting Month])) - It renders "2020/04/30" and

b) HCCurrentMonth = CALCULATE(SUM(HC_ACT[Headcount]),HC_ACT[TIME] = DATE(2020,04,30))

I would like to use the first measure to replace the date in the second measure, but this does not work, and gives the error "A function 'CALCULATE' has been used in a True/False expression that is used as a table filter expression."

How can I achieve the intended result?

thank you

Kevin

[Tahreem24]

Hi Kevin,

HCCurrentMonth = CALCULATE(SUM(HC_ACT[Headcount]), FILTER(HC_ACT,HC_ACT[TIME] = MAX(HC_ACT[TIME])))

[az38 · Accepted solution]

Hi @_Kevin_ 

try 

HCCurrentMonth = 
var _CurrentMonth = MAX((Info[Reporting Month]))
RETURN

CALCULATE(SUM(HC_ACT[Headcount]),HC_ACT[TIME] = _CurrentMonth )

or

HCCurrentMonth = CALCULATE(SUM(HC_ACT[Headcount]),FILTER(HC_ACT, HC_ACT[TIME] = SELECTEDVALUE([CurrentMonth]) ))

[_Kevin_]

Hi @az38 

Thanks, the first option works great.

I would have preferred to use the second option but I cannot use a measure in the SELECTEDVALUE and get  "Parameter is not the correct type"

Regards

Kevin

[az38]

@_Kevin_ 

try MAX() instead of SELECTEDVALUE() as @Tahreem24  mentioned

[_Kevin_]

Hi @az38 

I don't need either, I have now put the measure straight after "=" and it works too. 

regards

Kevin