Dear community,
I am looking for a solution to count or show a specific status depending on a start and end date.
Use case: data from a recruiting tool. Jobs are "open" as soon as there is a start date and "Filled" as soon as there is a "Date Closed".
I would like to achieve to find the count of jobs that were "open" at a certain date or date range when filtering by date dynamically and finally having also charts to show the evolution over time.
I have placed this issue several times and seen several similar posts but I could neither replicate the already existing solutions nor the ones that had been kindly provided to me. I am not sure if there is an issue in my date table (counting in fiscal years starting in July), the relationships or the formulas themselves.
Thank you very much in advance!!
Summary:
Date Table:
Hi @Svendu
Things will be different when you filter on a specific Date or a date range. When filtering only a specific date, the current solution 1 and solution 2 should work well as one date could only be a created date, or a closed date, or a date between those two dates. The status is easy to judge.
However, when you filter to a date range, let's take a month as an example, there are four situations for the status of a job Req. I draw a picture as below. If we select Jan 2020, the status of situation 4 is clear to be "Open" based on your description.
For situation 1, it was created and closed in the same month and the range between two dates are less than the month range, so do you want to count it as "Open" or "Filled" in Jan 2020?
For situation 2 and 3, their Req open ranges cross two months and neither calendar month is fully covered. So how do you want to count their status in two months?
These situations also happen when you expand the filtered range to a quarter or a year. Apparently the situation 2 in my example cross two different quarters and different years. You need to determine how you want to count the status for job Reqs that have the situation 1, 2, 3. Give us a specific result "Open" or "Filled" for each month. Then we can help provide a solution that can meet your need. We cannot make the decisions for you on whether it should be "Open" or "Filled".
Best Regards,
Community Support Team _ Jing
Hi Jing,
Thank you very much for your answer.
The scenarios you drew are exactly the behaviour I am trying to calculate.
It doe not have to show "Open" or "Filled" I thought it could be an option to calculate it easier if it was shown.
The rational would be to count the number of reqs that were open, but does not need to show "open". As per your example above it should count:
Is this feasible?
To answer the question regarding 2 and 3: As long as in a month it was open it should count as such even if it was filled inbetween.
Let me know if it is clear enough.
Unfortunately I could not upload the pbix, it tells me "the file type pbix is not supported". If there is a way to upload it I can send you the sample.
Helpful resources
Microsoft Fabric Learn Together
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Power BI Monthly Update - April 2024
Check out the April 2024 Power BI update to learn about new features.
Fabric Community Update - April 2024
Find out what's new and trending in the Fabric Community.
