I am trying to achieve something that I believe should not be so hard, but it will not work.

Until now we had a measure that calculated how many jobs had been ‘created’ and ‘executed’ in the same month. We did this by using the date-id’s:

CALCULATE('ia FactConv'[Sum of Jobs], LEFT('ia FactConv'[CreationDateId],6) = LEFT('ia FactConv'[ExecutedDateId],6))

But now we don’t want to check on calendar months but on bookmonths. So a bookmonth could be from 29th till 28th or from 3rd to 1st of the next month etc.
And then the above solution will not work anymore.

Next to the FactConv we also have a couple of date dimensions and these also contain a column with the bookmonth per unique date. So I was guessing something like this should work:

VAR CreationBookMonth = LOOKUPVALUE('ia DimCreaDate'[BookMonthNumber],'ia DimCreaDate'[DateId],'ia FactConv'[CreationDateId])

VAR ExecutionBookMonth = LOOKUPVALUE('ia DimExecDate'[BookMonthNumber],'ia DimExecDate'[DateId],'ia FactConv'[ExecutedDateId])

VAR Result = CALCULATE('ia FactConv'[Sum of Jobs], CreationBookMonth = ExecutionBookMonth)

Return Result

But it keeps giving errors on the search_value (the last argument of the LOOKUPVALUE). The documentation states that it should/could be a column, but in Visual Studio I am getting the suggestion that it should be measure. And when I continue anyway I get the message ‘A single value for CreationDateId in FactConv can not be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.’

So I have a couple of questions:

1. Why cant I use the LOOKUPVALUE as I expected?
2. What should I adjust to use it and make the above function work?
3. Is there any other (easier) way to achieve what I would like?