Help with calculations based on similar dates and other conditions

[benthomas]

Hi there,

I have data in the following format:

I find this hard to explain but one of the ways in which I need to display this data is in a simple bar graph showing the total number of nights per team.  The trouble I'm having is when there are multiple entries for the same team in an encompassing date range.  The reason for the additional entry/s is that one staff member only did two nights instead of three so an individual entry is created for them in order to still record the nights per person metric.

As per the above highlighted example, the 'Team' has spent in total 3 nights away but at the moment it's summing it as 5.  How am I able to calculate/display this number correctly whereby it looks at the total nights away for the 'Team' being 3 rather than summing it as 5.

The dates are the key here, if there are multiple entries for the same team during a date range that encompasses another for the same team, it can be assume that it should be concurrent rather than cumulative as far as the count goes.

I hope that makes sense.  The way we're recording this now allows for the night per person to be calculated and displayed no problems, but the nights per team is my challenge.

Cheers.

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

Total nights per team: = 
SUMX (
    ADDCOLUMNS (
        GROUPBY (
            Data,
            Data[Team],
            Data[Departure Date],
            "@maxreturndate", MAXX ( CURRENTGROUP (), Data[Return Date] )
        ),
        "@nights", INT ( [@maxreturndate] - Data[Departure Date] )
    ),
    [@nights]
)

[benthomas]

Thank you so much for that - I'm trying to teach myself this so it's hard to know which direction to go to get the results I'm after.  I am looking at the file you provided, am I correct in deploying this as a 'quick measure', or is there another better method to 'deploy' it?

I also understand some of your expression but not the whole thing, will it consider those circumstances where the departure and/or return date is different but still encompassed by a greater date range.

Eg.

Dep	Ret	Team	Staff
10/01/2022	20/01/2022	Team 12	5
12/01/2022	18/01/2022	Team 12	2

Thank you so much.