Forum Discussion

Brianoreilly's avatar
Brianoreilly
Helper II
7 years ago
Solved

If Statement using Calculated Measure and Date Sliceer

Hi Folks,   I need to build 2 dynamic measures which will allow me to assess if an employee was/is working within a time period.  The time period in questions, being dictated by the Min & Max Date...
Need Help
  • v-frfei-msft's avatar
    v-frfei-msft
    7 years ago

    Hi Brianoreilly,

     

    We can create the measures as below to achieve your goal.

     

    Start Date =
VAR mindate =
    MIN ( 'date'[Date] )
RETURN
    IF (
        ISBLANK ( MAX ( 'dataset'[Employee End Date] ) )
            && MAX ( 'dataset'[Employee Start Date] ) < mindate,
        mindate,
        MAX ( 'dataset'[Employee Start Date] )
    )

    End Date = 
VAR maxdate =
    MAX ( 'date'[Date] )
VAR mindate =
    MIN ( 'date'[Date] )
RETURN
    IF (
        ISBLANK ( MAX ( 'dataset'[Employee End Date] ) )
            && mindate >= MAX ( 'dataset'[Employee Start Date] ),
        maxdate
    )

     

    Also please find the pbix as attached.

     

    Regards,

    Frank

v-frfei-msft's avatar
v-frfei-msft
Community Support
7 years ago

Hi Brianoreilly,

 

We can create the measures as below to achieve your goal.

 

Start Date =
VAR mindate =
    MIN ( 'date'[Date] )
RETURN
    IF (
        ISBLANK ( MAX ( 'dataset'[Employee End Date] ) )
            && MAX ( 'dataset'[Employee Start Date] ) < mindate,
        mindate,
        MAX ( 'dataset'[Employee Start Date] )
    )

End Date = 
VAR maxdate =
    MAX ( 'date'[Date] )
VAR mindate =
    MIN ( 'date'[Date] )
RETURN
    IF (
        ISBLANK ( MAX ( 'dataset'[Employee End Date] ) )
            && mindate >= MAX ( 'dataset'[Employee Start Date] ),
        maxdate
    )

 

Also please find the pbix as attached.

 

Regards,

Frank