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:
This is a little old, but I have a possible solution. I was struggling to build a rolling count of expiring points (HR management call-out points). The points would expire after a set time window.
I had a date. and a point value. I duplicated the date into "expire date" and added my expiration value to it (i.e. expire date = date + N_days). I had a point value so I duplicated that entire column and multiplied iy by -1 (this makes it a negative counterpart to whatever is in "points"). I did this in excel before importing.
So it would look like this:
Name | Points | NegPoints | Date | ExpireDate
John Doe | 1 | -1 | 5/2/2024 | 7/31/2024
Jane Doe | 1 | -1| 5/11/2024 | 8/9/2024
After importing I unpivoted the two date fields. It lumped them under "Attribute" which I renamed to "DateType"
Name | Points | NegPoints | DateType | PtsDate
John Doe | 1 | -1 | Date | 5/2/2024
John Doe | 1 | -1 | ExpireDate | 7/31/2024
Jane Doe | 1 | -1 | Date | 5/11/2024
Jane Doe | 1 | -1 | ExpireDate | 8/9/2024
How to make the rolling window? I ran into many problems and found precious little help online for it. I created a new column as a measure called PointsCheck. Here's what I did:
Hi @Anonymous
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
Join our Fabric User Panel
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Power BI Monthly Update - June 2025
Check out the June 2025 Power BI update to learn about new features.
Fabric Community Update - June 2025
Find out what's new and trending in the Fabric community.
