Show number of requisition who were open

[checkner]

Hello,

I have the following table (example) with job requisitions who are open or closed with specific dates. Now I want to know how many requisitions were open in August (in a month, quarter, year..). In addition I want to be able to filter the visual by department etc., it is not just display a number. 

Correct is: 4 in August, 5 in September, 4 in October

ID	Status	Opend date	Closed date
123456	Open	01.10.2023
134567	Open	30.09.2023
849403	Closed	29.08.2023	28.09.2023
049458	Open	29.08.2023
234586	Closed	29.08.2023	13.09.2023
485967	Closed	29.08.2023	06.10.2023

[Idrissshatila]

Hello @checkner ,

try the following

measure = calculate ( countrows ( YourTableName), closed date = blank )

If I answered your question, please mark my post as solution, Appreciate your Kudos 👍

Follow me on Linkedin
Vote for my Community Mobile App Idea 💡

[checkner]

The problem with the calculation is that it only gives me the open ones. However, I need the jobs that were open in the respective months/quarters. 

Correct should be: 4 in August, 5 in September, 4 in October

[Anonymous · Accepted solution]

Hi @checkner ,

I created a sample pbix file(see the attachment), please check if that is what you want.

1. Create a date dimension table(DO NOT create any relationship with the fact table)

2. Create a measure as below to get the number of requisition who were open

Number of requisition who were open = 
VAR _seldate =
    SELECTEDVALUE ( 'Date'[Date] )
VAR _yearmonth =
    YEAR ( _seldate ) & FORMAT ( _seldate, "MM" )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[ID] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            YEAR ( 'Table'[Opend date] ) & FORMAT ( 'Table'[Opend date], "MM" ) <= _yearmonth
                && (
                    ISBLANK ( 'Table'[Closed date] )
                        || YEAR ( 'Table'[Closed date] ) & FORMAT ( 'Table'[Closed date], "MM" ) >= _yearmonth
                )
        )
    )

3. Create a line chart

Best Regards