Counting and summing days in periods, disregarding duplicates

[nikostou]

Hi,

I am trying to get an overview of the number of days my resources has been running in a given period (eg. week or month).

My data looks similar to this:

Resource	ID	StartDate	EndDate
KF4	1012	05-11-2021	08-11-2021
SL5	1215	10-11-2021	15-11-2021
KF4	1400	08-11-2021	11-11-2021
BV2	1589	13-11-2021	16-11-2021
BV2	1365	16-11-2021	20-11-2021
KF4	1485	14-11-2021	20-11-2021
NI8	1354	01-11-2021	14-11-2021
SL5	1845	20-11-2021	22-11-2021
NI8	1245	16-11-2021	19-11-2021
SL5	1784	22-11-2021	22-11-2021
BV2	1235	25-11-2021	30-11-2021

The biggest issue here is that I want to count the days of work, but if two IDs overlap, the day should only be counted once.

The result I would like to have should be something like this:

Resource	Days of work
KF4	14
SL5	9
BV2	14
NI8	18

Please let me know if you have any ideas!

Thank you 🙂 

[VahidDM]

Hi @nikostou 

If you want to disregarding overlaps, try this code:

** I added new rows to youe sample data to create overlaps, my table is as below:

Code:

Days of work =
VAR _RES =
    VALUES ( 'Table'[Resource] )
VAR _DT =
    CALENDAR ( MIN ( 'Table'[StartDate] ), MAX ( 'Table'[EndDate] ) )
VAR _DTA =
    CROSSJOIN ( _RES, _DT )
VAR _RD =
    ADDCOLUMNS (
        _DTA,
        "In",
            CALCULATE (
                COUNTROWS ( 'Table' ),
                FILTER (
                    'Table',
                    'Table'[Resource] = EARLIER ( [Resource] )
                        && 'Table'[StartDate] <= [Date]
                        && 'Table'[EndDate] >= [Date]
                )
            )
    )
VAR _RDWB =
    FILTER ( _RD, [In] <> BLANK () )
RETURN
    COUNTROWS ( _RDWB )

result:

Download file: https://gofile.io/d/HUTAzY

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[nikostou]

Hi @Anonymous 
Unfortunately I cannot get this to give me the right result.

[Samarth_18]

Hi @nikostou ,

Create a column with below code:-

Column = DATEDIFF('Table'[StartDate],'Table'[EndDate],DAY)

Now create a measure with below code:-

Measure = SUM('Table'[Column])+2

Output:-

Thanks,

Samarth

[nikostou]

Hi @Samarth_18 

Unfortunately this does not work.

I am not sure why you are adding 2 in the measure?

Can you explain that to me?

[smpa01]

@nikostou  I don't see any overlapping IDs here in the sample and therefore I don't think it is represtative of the issue you described.

Can you please provide something that is represtative of the challenge you described.

[nikostou]

Hi @smpa01 

It is overlapping in the sense that if one resource ends one ID and starts another, then this day/date should not be counted twice.

If I make a measure that counts the days of work per line and then simply sums it up at the end, then in this case this date/day will be counted twice.

Like this:

Resource	ID	StartDate	EndDate	Working days
KF4	1012	05-11-2021	08-11-2021	4
SL5	1215	10-11-2021	15-11-2021	6
KF4	1400	08-11-2021	11-11-2021	4
BV2	1589	13-11-2021	16-11-2021	4
BV2	1365	16-11-2021	20-11-2021	5
KF4	1485	14-11-2021	20-11-2021	7
NI8	1354	01-11-2021	14-11-2021	14
SL5	1845	20-11-2021	22-11-2021	3
NI8	1245	16-11-2021	19-11-2021	4
SL5	1784	22-11-2021	22-11-2021	1
BV2	1235	25-11-2021	30-11-2021	6

The wrong result would look like this:

Resource	Days of work	Sum of working days
KF4	14	15
SL5	9	10
BV2	14	15
NI8	18	18

Days of work is the days that the resource has been working.

Sum of working days is summing the individual count of days pr. line.

Days of work is the result I am looking for.

[smpa01]

@nikostou  this will give you what you need

Measure = 
COUNTROWS (
    SUMMARIZE (
        GENERATE (
            'Table',
            DATESBETWEEN ( 'Calendar'[Date], 'Table'[StartDate], 'Table'[EndDate] )
        ),
        'Table'[Resource],
        'Calendar'[Date]
    )
)

[nikostou]

Hi @smpa01

Unfortunately this does not work.

If a resource has more IDs one one day, this will count these days multiple times.

This is wrong..

[smpa01]

@nikostou provide sample data accordingly