Total Employee Detail

[nix]

I have a voyage table where crew sign-on date,sign off date column is present.
One Crew have done different voyage
I have dim date table also
Now what i want is a date slicer for selected date range(which i can do by fixing a slicer with dim date)
as of now i have related dim date (date column to sign-on column from voyage table)
Now what i  want in table visual is 
the selected date range should filter crew in between that given range of date
means (condition-crew can sign on before selected (from date) or can sign on before to date)
crew sign off date should be blank or less then to date and greater then from date
it should give crew list that they were in signon and sign off in between selected from date and to date
I tried this measure also but its not working

Measure 19 = var earliestDate = MIN(Dimdate[Date])

 Var latestDate=Max(Dimdate[Date])

var _result1 =

CALCULATE(

    DISTINCTCOUNT('Crew Voyage Detail'[crew_id]),

       ALL( 'Crew Voyage Detail'),

        'Crew Voyage Detail'[SignOn_DATE] <= earliestDate ||'Crew Voyage Detail'[SignOn_DATE] <= latestDate

        && (( 'Crew Voyage Detail'[SignOFF_DATE] > earliestDate && 'Crew Voyage Detail'[SignOFF_DATE] < latestDate)|| ISBLANK('Crew Voyage Detail'[SignOFF_DATE]) )

    )

RETURN _result1

How to do this
Thanks in Advance

[v-yiruan-msft]

@some_bih Thanks for your contribution on this thread.

Hi @nix ,

You can follow the steps below to get it:

1. Please remove the relationship between the table 'Dimdate' and 'Crew Voyage Detail' base on the field [Date] and [SignOn_DATE]

2. Update the formula of measure [Measure 19]

Measure 19 =
VAR _seldate =
    SELECTEDVALUE ( Dimdate[Date] )
VAR _result1 =
    CALCULATE (
        DISTINCTCOUNT ( 'Crew Voyage Detail'[crew_id] ),
        FILTER (
            ALLSELECTED ( 'Crew Voyage Detail' ),
            'Crew Voyage Detail'[SignOn_DATE] <= _seldate
                && (
                    'Crew Voyage Detail'[SignOFF_DATE] >= _seldate
                        || ISBLANK ( 'Crew Voyage Detail'[SignOFF_DATE] )
                )
        )
    )
RETURN
    _result1

3. Create a line chart( X-axis: the field [Date] of the table 'Dimdate'; Y-axis: [Measure 19])

Best Regards

[nix]

Hi @v-yiruan-msft 
Thank you so much for your try and solution
As i am taking Dim date as a slicer with (between value)(mean from date and to date range)
I use this DAX 

Test =

VAR Erali = MINX(VALUES(Dimdate[Date]), Dimdate[Date])

VAR Late = MAXX(VALUES(Dimdate[Date]), Dimdate[Date])

VAR _result1 =

    CALCULATE (

        DISTINCTCOUNT ( 'Crew Voyage Detail'[crew_id] ),

        FILTER (

            ALLSELECTED ( 'Crew Voyage Detail' ),

            'Crew Voyage Detail'[SignOn_DATE] <= Late

                && (

                    'Crew Voyage Detail'[SignOFF_DATE] >= Erali

                        || ISBLANK ( 'Crew Voyage Detail'[SignOFF_DATE] )

                )

        )

    )

RETURN

    _result1

And even dint apply relation with voyage table 
See here sign off date is also less then from date
Why i am not getting this
Please Help

[v-yiruan-msft · Accepted solution]

Hi @nix ,

Please update the formula of measure [Test] as below and check if it can return the expected result...

Test = 
VAR Erali =
    MIN ( Dimdate[Date] )
VAR Late =
    MAX ( Dimdate[Date] )
VAR _result1 =
    CALCULATE (
        DISTINCTCOUNT ( 'Crew Voyage Detail'[crew_id] ),
        FILTER (
            'Crew Voyage Detail',
            'Crew Voyage Detail'[SignOn_DATE] <= Late
                && (
                    'Crew Voyage Detail'[SignOFF_DATE] >= Erali
                        || ISBLANK ( 'Crew Voyage Detail'[SignOFF_DATE] )
                )
        )
    )
RETURN
    _result1

Best Regards

[some_bih]

Hi @nix without model and other details, it is hard to spot your issue.

Still, try test measure below and check picture.

Measure 19 test = 

var earliestDate = MIN(Dimdate[Date])

Var latestDate=Max(Dimdate[Date])

var _result1 =

CALCULATE(

    DISTINCTCOUNT('Crew Voyage Detail'[crew_id]),

       ALL( 'Crew Voyage Detail'),

        'Crew Voyage Detail'[SignOn_DATE] => earliestDate 

||'Crew Voyage Detail'[SignOn_DATE] <= latestDate

        && (( 'Crew Voyage Detail'[SignOFF_DATE] > earliestDate && 

'Crew Voyage Detail'[SignOFF_DATE] < latestDate)

|| ISBLANK('Crew Voyage Detail'[SignOFF_DATE]) )

    )

RETURN _result1

[nix]

Exactly,

How can i do with some date filter and that should give these crew details who signed on on or before max of selected date
any how i have to relate date column with voyage table right,thats why i did that