Longitudinal injuries - how many players are injured at any timepoint

[Maestro]

Hi all, at the moment I am working on injury data over multiple seasons.

Key information for everyone is: how many players do you have available. Therefore, we want to see how many players are injured at any moment in time (is it generally 3 or 8 players that you can't use). Therefore I want a graph as shown below (made in Excel): over the timeframe I want the # of players that are injured. For this purpose I have the date of occurence and date of return-to-play. In the graph you can see on the x-axis the date and on the y-axis the amount of injuries for each team (team is different colors). For example: a player in the 'blue' team is injured from 1st of June till 3rd of August --> he should be counted as 'one injury' for the whole time-period, not just the start of his injury. In addition to this player more players in this team become injured and add up.

I have already tried distinctcount, count or datesbetween combinations of DAX formulas, but I am too much of a beginner to get it working. I also tried to generate a new table as 'count', with date filters and summarizing countrows, but everything I try: it is not valid, unfortunately. 

Table = 
VAR ExpandedTable =     
    GENERATE(
        CALENDAR(DATE(2010,1,1),DATE(2025,12,31)),
        FILTER(
            'Blessures',
            [Date]>='injuries'[start_injury] &&
            [Date]< IF(ISBLANK('injuries'stop_injury),TODAY(),'injuries'stop_injury)
            )
            )
            
RETURN 
    SUMMARIZE(  
        ExpandedTable,
        [Date] ,
        [Team], 
        "Count",COUNTROWS('injuries')
        )   

I hope I showcased the problem clearly. If anything is still unclear, please let me know! Thanks in advance! 

[tamerj1 · Accepted solution]

@Maestro 
Here is a sample file with the solution https://www.dropbox.com/t/tgnDTNTOWcYDK82x

Count Injuries = 
SUMX (
    'Date',
        SUMX (
            Blad1,
            VAR CurrentDate = 'Date'[Date]
            RETURN
                IF ( 
                    Blad1[Injury_start] <= CurrentDate
                        && OR ( ISBLANK ( Blad1[Injury_end] ), Blad1[Injury_end] > CurrentDate ),
                    1,
                    0
                )
        )
)

[Maestro]

I feel so stupid. I tried for 30 minutes, but never seemed to do what I wanted. At the end I only had to delete the relation between the date table and injury table... Thanks a lot for the help! 

[tamerj1]

Hi @Maestro 

You have to have a date table. Then you may try

Count Injuries =
SUMX (
    'Date',
    VAR CurrentDate = 'Date'[Date]
    RETURN
        SUMX (
            injuries,
            injuries[start_injury] <= CurrentDate
                && OR ( ISBLANK ( injuries[stop_injury] ), injuries[stop_injury] > CurrentDate )
        )
)

[Maestro]

It shows the error that the function SUMX cannot use the type "Boolean". Btw. I do have a date table, so that should not be the problem. 

[tamerj1]

@Maestro 
Apologies i missed the IF 

Count Injuries =
SUMX (
    'Date',
    VAR CurrentDate = 'Date'[Date]
    RETURN
        SUMX (
            injuries,
            IF (
                injuries[start_injury] <= CurrentDate
                    && OR ( ISBLANK ( injuries[stop_injury] ), injuries[stop_injury] > CurrentDate ),
                1,
                0
            )
        )
)

[AilleryO]

Hi,

Maybe try with : 

&& ( ISBLANK ( injuries[stop_injury] ) || injuries[stop_injury] > CurrentDate )
@tamerj1 why do you prefer a SUMX instead of COUNTX ?

[tamerj1]

@AilleryO 

Two reasons. I trust it more and it performs faster. However, can be much efficient using SUMMARIZE

[Maestro]

I can show an outline of the table below if that works: EDIT: I use the DD-MM-YYYY format
- from the 28th of August it should show one injury, until 6th of September. Then it should show two injuries 10th of September. etc.

Player	Team	Season	Start injury	End injury	Time-loss	Location	Activity
A	1	2021-2022	28-8-2021	10-9-2021	13	Ankle	Training
B	1	2021-2022	6-9-2021	13-9-2021	5	Ankle	Match
C	2	2021-2022	9-9-2021	15-9-2021	6	Ankle	Training
D	3	2021-2022	13-9-2021	17-9-2021	4	Thigh	Match
E	3	2021-2022	13-9-2021	24-9-2021	11	Knee	Match

Currently I have two active tables: the one you see above and one date-table (with all possible variations of notations of dates from 2010 till 2025). This has a one-to-many relation with the injury table. 

[AilleryO]

Hi,

I add some datas to your dummy data to check, and this seems to work well :

Count of Injuries at date (mesure) = 

VAR DateCurrent = SELECTEDVALUE( TabInjuries[start injury] )

RETURN

CALCULATE( COUNT( TabInjuries[Player] ) , ALL( TabInjuries ) , 

TabInjuries[Start injury] <= DateCurrent && 

( TabInjuries[End injury] > DateCurrent || ISBLANK( TabInjuries[End injury] ) )

)

Gives that :

In my table (screenshot) you can see two columns because I made calculation as well as column (depending on your needs).

Column will be :

Count of Injuries at date (column) = 

VAR DateCurrent = TabInjuries[start injury]

RETURN

CALCULATE( COUNT( TabInjuries[Player] ) , ALL( TabInjuries ) , 

TabInjuries[Start injury] <= DateCurrent && 

OR( TabInjuries[End injury] > DateCurrent , ISBLANK( TabInjuries[End injury] ) )

)

If that works on your model, do not forget to mark the post as Solved.

Thanks and enjoy

[Maestro]

Tried both options, but it does not seem to replicate what I did in Excel. It seems to have too much deviations up and down (long injury does not count for the whole period). Maybe since the data is only added for any 'start of injury', but I am not sure. On the other side, since there are new injuries every week this should not make much of a difference 🤔.

I uploaded a bigger dataset, also suggested by @AilleryO  (not all true values, but it should provide enough data and 'real' information). Docs Spreadsheet (If I missed any private settings, please let me know 😅)

[AilleryO]

Hi,

Would be great if you could share some sample data (without any sensitive infos) using One Drive, Google Drive...

How many tables in your model and is one of them a Date table with a relationship to your injuries table ?

Let us know...