Longitudinal injuries - how many players are injured at any timepoint
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!
