Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Calculated Column Based on a DateTime event in a DateTime Range

Hi PowerBI DAX Experts,   I have a table with fields: PersonID, Role, ValidFromDateTime and ValidToDateTime.   If the person's role is currently valid then the ValidToDateTime is blank (null). ...
Need Help
  • AlB's avatar
    AlB
    6 years ago

    Anonymous 

    Sure. You're right

    Role =
CALCULATE (
    DISTINCT ( Table1[Role] ),
    FILTER (
        Table1,
        Table1[ValidFromDateTime] <= Table2[EventDateTime]
            && ( Table1[ValidToDateTime] > Table2[EventDateTime] || ISBLANK ( Table1[ValidToDateTime] ) )
            && Table1[ID] = Table2[ID]
    )
)

     

    Please mark the question solved when done and consider giving kudos if posts are helpful.

    Cheers  Datanaut

Anonymous's avatar
Anonymous
Not applicable
6 years ago

AlBPlease see my reply above.

AlB's avatar
AlB
Icon for Community Champion rankCommunity Champion
6 years ago

Anonymous 

Sure. You're right

Role =
CALCULATE (
    DISTINCT ( Table1[Role] ),
    FILTER (
        Table1,
        Table1[ValidFromDateTime] <= Table2[EventDateTime]
            && ( Table1[ValidToDateTime] > Table2[EventDateTime] || ISBLANK ( Table1[ValidToDateTime] ) )
            && Table1[ID] = Table2[ID]
    )
)

 

Please mark the question solved when done and consider giving kudos if posts are helpful.

Cheers  Datanaut