Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsMFelix 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
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
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |