Custom Column: Return value if date falls between two date fields in another table

[AW1976NOVA]

Hi,

I am trying to create a custom column where it will return a the ClaimData[PlanYear] when HPP_Finance_All[PaidMonth] is between ClaimData[PlanYearStart] and ClamData[PlanYearEnd].  Another clause of the column needs to include where the HPP_Finance_All[GroupID] = ClaimData[GroupID].

Can you please assist?

[v-rzhou-msft · Accepted solution]

Hi @AW1976NOVA 

I build a sample to have a test.

ClaimData Table:

HPP_Finance_All Table:

Create a custom column by M query.

let
 _CurrentID = [GroupID], _Month = [PaidMonth] 
in
Table.SelectRows(ClaimData, each _[GroupID] = _CurrentID
and _[PlanYearStart] <= _Month and _[PlanYearEnd]>=_Month){0}[PlanYear]

If there is no PlanYear match from ClaimData, it will show error.

So we need to replace error and get final result.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@AW1976NOVA Maybe Lookup Range: LOOKUPVALUE Range - Microsoft Power BI Community