How to dynamically track activity based on start date and end date

[rohan23]

Hello and good day. In my current application I have a column tracking employee start date and another tracking employee end date. I want to try and display a graph that shows employee activity on a monthly basis. To that end, I tried using a calculated column called Activity ratio with the formula: 

Activity Ratio =

IF(ISBLANK(EmployeeRecords[End Date]) || (DigitalEmployeeRecords[End Date].[Date]>TODAY()) ,1,0)

However, when using a stacked column chart, my active employee count remains the same for the year of 2025 despite having an employee whose end date is 30th September.

I am currently thinking of changing the calculated column to a measure and changing the TODAY() to something else. Would this approach be advisable or is there something else I can do?

Thank you for your time

[rohan23]

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. 

[v-aatheeque · Accepted solution]

Hi @rohan23 ,

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!

[rohan23]

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=pQ9eSnfAhnc

[v-aatheeque]

Hi @rohan23 ,

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!

[lbendlin]

You can use Gantt charts or Deneb for that if you want a graphical solution.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

Need help uploading data? https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...