Calculate number of teams on hire based off of start and end date

[dfeuer]

I am trying to calculate the number of "team days" per day based off of a start and end date for each team. 

Team        Start Date      End Date

Team 1      1/1/2016        12/31/2016
Team 2      4/13/2016      11/19/2016
Team 3      5/8/2016        11/29/2016
Team 4      4/22/2016      11/19/2016

in this case, for example, Jan 1 would have 1 day, since only 1team is working then, but June 12 would have 4 since all 4 teams are working. I am trying to get these values for each day of the year.

Thank you!

[AlbertoFerrari]

MFelix answer is right, if you are interested in performance because you are working on a large dataset, you might also take a look at this article I wrote some time ago: https://www.sqlbi.com/articles/analyzing-events-with-a-duration-in-dax/, But, if yours is a small dataset, there's no need to superoptimize it and you can safely go with the simpler formula.

Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

[MFelix · Accepted solution]

Hi @dfeuer,

Create a Dates table (without relationship) put the date of this table in youe axis/table/... and then add this measure

Teams =
VAR dates_slicer =
    MIN ( 'Calendar'[Date] )
RETURN
    CALCULATE (
        COUNTA ( Teams[Team] );
        Teams[Start Date] <= dates_slicer;
        Teams[End Date] >= dates_slicer
    )

Should give you what you need.

Regards

MFelix