Forum Discussion
How to dynamically track activity based on start date and end date
Hi Anonymous ,
Follow these steps
Make sure calendar table is correctly mapped with from Employee table
use DAX measure instead of calculated column
Active Employees =
CALCULATE(
COUNTROWS(Employee),
(ISBLANK(Employee[End Date]) || Employee[End Date] >= MIN('Calendar'[Date]))
)
Expected output :If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.
If you continue to face issues, feel free to reach out to us for further assistance!
I have one table for employees as follows:
| Employee Name | Start Date | End Date | Activity Ratio |
| Bruce Wayne | 1/6/2023 | - | 1 |
| Clark Kent | 3/1/2014 | 7/2/2024 | 0 |
| Diana Prince | 5/1/2023 | - | 1 |
| Barry Allen | 5/1/2014 | 8/8/2026 | 1 |
| John Stewart | 8/9/2019 | 7/3/2025 | 0 |
I have another table for a calendar as follows:
| Date | Year | Month | Year Month | Cumulative Employee |
| 1/1/2014 | 2014 | 1 | 01/2014 | 0 |
| 2/1/2014 | 2014 | 1 | 01/2014 | 0 |
| 3/1/2014 | 2014 | 1 | 01/2014 | 1 |
| 4/1/2014 | 2014 | 1 | 01/2014 | 1 |
| 5/1/2014 | 2014 | 1 | 01/2014 | 2 |
| 6/1/2014 | 2014 | 1 | 01/2014 | 2 |
Currently, I am plotting a graph containing the maximum cumulative ratio on the y-axis and a date hierarchy containing year/month/date on the x-axis.
Hi Anonymous ,
Follow these steps
Make sure calendar table is correctly mapped with from Employee table
use DAX measure instead of calculated column
Active Employees =
CALCULATE(
COUNTROWS(Employee),
(ISBLANK(Employee[End Date]) || Employee[End Date] >= MIN('Calendar'[Date]))
)
Expected output :If this post was helpful, please consider marking Accept as solution to assist other members in finding it more easily.
If you continue to face issues, feel free to reach out to us for further assistance!
Thank you for the reply I feel like it explains my problem quite well. Nonetheless I managed to already find a solution mostly using this video:
https://www.youtube.com/watch?v=pQ9eSnfAhncHi Anonymous ,
It looks like your problem has been solved, please mark the helpful reply and accept it as solution, it will be helpful for other members of the community who have similar problems as yours to solve it faster.
Thank you very much for your kind cooperation!