Measure based on 2 date columns

[Anonymous]

Hi all, 

I am trying to create a measure that shows the number of Job requests based on 2 Date columns.

 I'd like to have as result for Oct/18  76 Job request. 

 I have created a relation between a CALENDAR tab and REC 1 (using the request date as a key point on REC 1) and the following measures: 

DATE DATE:

Data Date = LASTDATE ( ALL ( 'Rec 1'[Request Date]) )

ON THE JOB:

ON THE JOB = IF (
    [Data Date] >= MIN ( 'Calendar'[Date] ),
    CALCULATE (
        DISTINCTCOUNT ( 'Rec 1'[Reference number] ),
        FILTER (
            'Rec1',
            COUNTROWS (
                FILTER (
                    RELATEDTABLE ( Rec 1),
                    'Rec 1'[Request Date] <= MAX ( Calendar[Date] )
                        && (
                            ISBLANK ( 'Rec 1'[Complete Date]) 
                                || 'Rec 1'[Complete Date] >= MAX ( Calendar[Date] )
                        )
                )
            )
                > 0
        )
    )
)

The problem is that this measure is retuning a number shorter than the right one. 

ON THE JOB calculation is based the month select on the slicer (Calendar tab), it should count all Job request that have : 

1. Request date same as the slicer

2. Complete date as same as the Slicer

3. Complete date blank.

In the sample attached, it should return 76 requests for Oct/18, the job reference number is highlited in blue for the right return results. 

Any help is much appreciate. 

SAMPLE DATA BASE (Excel)

Thanks ! 

[v-cherch-msft · Accepted solution]

Hi @Anonymous

You may refer to below measure. Delete the relationship between the two tables.

ON THE JOB =
CALCULATE (
    COUNT ( 'Rec 1'[Reference Number] ),
    FILTER (
        'Rec 1',
        'Rec 1'[Request Date] <= ENDOFMONTH ( Calendar[Date] )
            && (
                MONTH ( 'Rec 1'[Complete date] ) >= MONTH ( SELECTEDVALUE ( 'Calendar'[Date] ) )
                    || 'Rec 1'[Complete date] = BLANK ()
            )
    )
)

Regards,

Cherie

[Anonymous · Accepted solution]

Thanks you ! 

It works properly. 

[Ashish_Mathur]

Hi,

Why should the answer be 76?  It should be 70.

1. When the Completed data is filtered on Blanks, the number of rows are 61
2. Clear the previous filter, filter the Request date on Oct and remove blanks from the Complete date column.  Number of rows left will be 2
3. Clear the previous filter, filter the Completed date column on Oct.  Number of rows left will be 7

[Anonymous]

Hi Ashish, 

Because to calculate for Oct/18, you should filter all requests that have Request Date that until Oct/18 (included) and Complete date as Oct, blank or beyond. 

In the Sample: 

1. Total of Job Request are 90. 

2. Request date on Oct or before are 88; 

3. Request date on Oct or before and Complete date as Oct, blank or beyond is 76. 

For example for September/18: 

1. Total of Job Request are 90. 

2. Request date on Sep or before are 71; 

3. Request date on Sep or before and Complete date as Sep, blank or beyond is 68. 

[Ashish_Mathur · Accepted solution]

Hi,

You may download my PBI file from here.  I have maintained the relationship with the Date Table.

Hope this helps.

[v-cherch-msft · Accepted solution]

Hi @Anonymous

You may refer to below measure. Delete the relationship between the two tables.

ON THE JOB =
CALCULATE (
    COUNT ( 'Rec 1'[Reference Number] ),
    FILTER (
        'Rec 1',
        'Rec 1'[Request Date] <= ENDOFMONTH ( Calendar[Date] )
            && (
                MONTH ( 'Rec 1'[Complete date] ) >= MONTH ( SELECTEDVALUE ( 'Calendar'[Date] ) )
                    || 'Rec 1'[Complete date] = BLANK ()
            )
    )
)

Regards,

Cherie

[Anonymous · Accepted solution]

Thanks you ! 

It works properly.