Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 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
    7 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
7 years ago

Hi AIB,

 

Thanks for your response.  In your response, shouldn't the ISBLANK part be:
ISBLANK ( Table1[ValidToDateTime] )
?

 

Regarding the logic of role, the role is the person's role when the event occured.

 

Thanks,
Trystan.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

AlBPlease see my reply above.

  • AlB's avatar
    AlB
    Icon for Community Champion rankCommunity Champion
    7 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