cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Dynamic count based on open and start date with date filter slicer

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:

Solution 1 proposed =
VAR _N1 =CALCULATE (COUNT ( Jobs[Job Req ID] ),
FILTER (ALL ( Jobs),
Jobs[Date Created]>= SELECTEDVALUE ( 'Fiscal Year Table'[Date] )
&& [Closed Date] = BLANK ()
&& [Status] = "Open"
)
)
VAR _N2 =CALCULATE (COUNT ( Jobs[Job Req ID] ),
FILTER (ALL ( Jobs ),
[Date Created] <= SELECTEDVALUE ( 'Fiscal Year Table'[Date] )
&& [Closed Date] >= SELECTEDVALUE ( 'Fiscal Year Table'[Date] )
&& [Status] = "Filled"
)
)
RETURN
_N1 + _N2

Solution 2 proposed =
VAR _MAXDATE =
MAX ( 'Fiscal Year Table'[Date] )
RETURN
IF (
MAX ( Jobs[Date Created] ) <= _MAXDATE,
IF (
MAX ( Jobs[Closed Date] ) = BLANK ()
|| MAX ( Jobs[Closed Date] ) >= _MAXDATE,
"Open",
"Filled"
),
"Filled"
)

Date Table:

Fiscal Year Table = CALENDARAUTO(6)
Fiscal Month N° = (MONTH(EDATE('Fiscal Year Table'[Date], -6)))
Fiscal Quarter = "Q" & (QUARTER(EDATE('Fiscal Year Table'[Date], -6)))
Fiscal Year =
VAR CurrYear = RIGHT(YEAR('Fiscal Year Table'[Date]),2)
VAR LastYear = RIGHT(YEAR('Fiscal Year Table'[Date])-1,2)
VAR NewYear = RIGHT(YEAR('Fiscal Year Table'[Date])+1,2)
VAR FiscalYear =
SWITCH(
TRUE(),
MONTH('Fiscal Year Table'[Date]) >= 4,
CurrYear & "-" & NewYear,
LastYear & "-" & CurrYear
)
RETURN
FiscalYear

5 REPLIES 5
Community Support

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

Frequent Visitor

Hi Jing,

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.

Frequent Visitor

Hi @v-jingzhang ,

MAybe additionally, I saw the following post from last year that looked promising, but again, I could not make it work for my case:

https://community.powerbi.com/t5/Desktop/Filtering-records-based-on-a-record-date-within-another-rec...

Thank you very much for all your efforts.

Sven

Frequent Visitor
 Job Req ID Status Date Created Closed Date 1 Filled 1.16.2018 3.8.2018 2 Filled 1.16.2018 4.17.2018 3 Filled 1.16.2018 6.20.2018 4 Filled 1.23.2018 3.6.2018 5 Filled 1.25.2018 3.19.2018 6 Filled 2.12.2018 6.25.2018 7 Filled 2.12.2018 6.13.2018 8 Filled 2.13.2018 7.5.2018 9 Filled 2.14.2018 5.8.2018 10 Filled 2.19.2018 6.25.2018 11 Filled 2.19.2018 6.27.2018 12 Filled 2.19.2018 5.29.2018 13 Filled 2.19.2018 4.2.2018 14 Filled 2.19.2018 4.17.2018 15 Filled 2.19.2018 4.13.2018 16 Filled 2.19.2018 6.7.2018 17 Filled 2.19.2018 8.1.2018 18 Filled 2.20.2018 4.2.2018 19 Filled 2.22.2018 7.16.2018 20 Filled 2.22.2018 4.13.2018 21 Filled 3.14.2018 7.20.2018 22 Filled 4.5.2018 5.16.2018 23 Filled 4.5.2018 7.2.2018 24 Filled 4.12.2018 6.25.2018 25 Filled 4.19.2018 6.6.2018 26 Filled 4.27.2018 5.11.2018 27 Filled 5.3.2018 6.11.2018 28 Filled 5.16.2018 7.5.2018 29 Filled 5.16.2018 6.6.2018 30 Filled 6.14.2018 7.23.2018 31 Filled 6.28.2018 7.10.2018 32 Filled 6.29.2018 8.21.2018 33 Filled 7.2.2018 9.11.2018 34 Open 7.12.2019 35 Filled 7.15.2019 12.19.2019 36 Open 7.15.2019 12.2.2019 37 Filled 7.15.2019 9.24.2019 38 Filled 7.15.2019 9.24.2019 39 Filled 7.15.2019 2.4.2020 40 Filled 7.15.2019 10.2.2019 41 Filled 7.16.2019 3.2.2020 42 Filled 7.16.2019 8.6.2019 43 Filled 7.16.2019 8.20.2021 44 Filled 7.16.2019 9.24.2019 45 Filled 7.16.2019 8.15.2019 46 Filled 7.16.2019 9.24.2020 47 Filled 7.16.2019 3.9.2020 48 Filled 7.16.2019 4.29.2020 49 Filled 7.16.2019 2.28.2020 50 Filled 7.16.2019 7.18.2019 51 Filled 7.17.2019 8.23.2021 52 Filled 7.18.2019 11.12.2019 53 Filled 7.18.2019 10.7.2019 54 Filled 7.18.2019 12.9.2019 55 Filled 7.18.2019 12.3.2020 56 Filled 7.18.2019 10.31.2019 57 Open 7.18.2019 11.6.2019 58 Open 7.18.2019 12.16.2019 59 Filled 7.19.2019 10.1.2019 60 Filled 7.23.2019 10.1.2019 61 Filled 7.26.2019 8.19.2021 62 Filled 7.26.2019 4.30.2020 63 Filled 7.26.2019 4.1.2020 64 Filled 8.5.2019 3.9.2020 65 Filled 8.5.2019 7.14.2021 66 Filled 8.5.2019 2.22.2021 67 Filled 8.7.2019 10.31.2019 68 Filled 8.7.2019 4.28.2020 69 Filled 8.8.2019 9.24.2019 70 Filled 8.8.2019 2.24.2020 71 Filled 8.8.2019 2.24.2020 72 Filled 8.8.2019 3.4.2020 73 Filled 8.12.2019 4.28.2020 74 Filled 8.16.2019 10.31.2019 75 Filled 8.16.2019 1.30.2020 76 Filled 8.21.2019 10.31.2019 77 Filled 8.26.2019 11.26.2019 78 Filled 8.27.2019 9.16.2021 79 Filled 8.27.2019 9.16.2021 80 Filled 8.27.2019 9.16.2021 81 Filled 8.27.2019 9.16.2021 82 Filled 8.27.2019 9.16.2021 83 Filled 8.27.2019 9.16.2021 84 Filled 8.27.2019 9.16.2021 85 Filled 8.27.2019 9.16.2021 86 Filled 8.27.2019 9.16.2021 87 Filled 8.30.2019 11.20.2019 88 Filled 9.2.2019 11.4.2019 89 Filled 9.4.2019 2.18.2020 90 Filled 9.4.2019 8.6.2020 91 Filled 9.10.2019 2.17.2020 92 Filled 9.17.2019 10.17.2019 93 Filled 9.18.2019 3.10.2020 94 Filled 9.20.2019 4.8.2020 95 Filled 9.20.2019 3.17.2020 96 Filled 9.23.2019 3.10.2020 97 Filled 9.25.2019 1.30.2020 98 Filled 9.26.2019 11.20.2019 99 Filled 9.27.2019 3.10.2020
Frequent Visitor

If anyone can tell me how to upload the pbix, I can send the file, but it tells me it is not supported...

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.