Forum Discussion
How to dynamically track activity based on start date and end date
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:
Thank you for your time
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!
5 Replies
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-Forum/ba-p/963216
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523I 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=pQ9eSnfAhnc
