Calculate the count of Open status using date selection filter with DAX

[Pbrit]

I have an SA table that overrides the record when status changes from open to closed and vice versa, I have manged to get the changing data records using SSIS , now I want to count the records that have status open during a selected period using a date filter, sample table is below.

Explanation to Table:

SA: B was open from 20th jan 2023 to 25th jan 2023 and was closed, SA: D was open from 1st Feb 2023 to 5th Feb 2023 and was closed, the rest of the SA's status remained unchanged.

No	Id	StartDate	EndDate	SA	Status
1	1	15/01/2023		A	Open
2	2	20/01/2023	25/01/2023	B	Open
3	3	25/01/2023		C	Open
4	4	01/02/2023	05/02/2023	D	Open
5	5	05/02/2023		E	Open
6	2	25/01/2023		B	Closed
7	4	05/02/2023		D	Closed

I Have the table above which contains SA, the status, the start date and end date,

I want to count the SA with status open when I do a date selection filter from 1st Jan 2023 to 31st Jan 2023,
what I expect for this count is No: 1,2,3 and for date selection filter from 1st Feb 2023 to 20th Feb 2023 is No 4 ,5.

I would appreciate any help on how to go about this as it has baffled me for long. If you require additional information, I would be glad to provide as well.

Thanks

[Pbrit]

Good day @some_bih , Please can you see if you can help with this challenge I am facing. Thanks 

[some_bih]

Hi @Pbrit please try measure below. Adjust your table name for Sheet2, and create Calendar / Date Table if you do not have it

Test =

VAR __start_date = MIN('Date'[Date])

VAR _end_date = MAX('Date'[Date])

VAR __selected_status = SELECTEDVALUE(Sheet2[Status])

RETURN

CALCULATE(

    DISTINCTCOUNT(Sheet2[SA]),--calculation of distinct count for SA

    FILTER(

        'Sheet2',

        'Sheet2'[Status] = __selected_status &&

        (

            'Sheet2'[EndDate] <= _end_date &&

            'Sheet2'[StartDate] >= __start_date

        )

    )

)

Output

Did I answer your question? Kudos appreciated / accept solution!

[Pbrit]

Thanks @some_bih for the fast response, is there a way to add ALL or any filter that would include the SA's that were open from say 1 Jan 2022 to 20 Feb 2023, when i choose 1st feb 2023 to 28th feb 2023 on the date filter.  with the current solution it only gives the sa open for the current month only.

[some_bih]

Hi @Pbrit to reduce retesting please provide expected outpu for input

[Pbrit]

@some_bih okay, Using the above code you provide , what i am expecting is if I select the period Feb 2023 with the date slicer, the output for open SA's should be 1000. 

Date	Open SA
Sept 2022	100
oct 2022	120
Nov 2022	200
Dec 2022	250
Jan 2023	200
Feb 2023	130
Total	1000

[some_bih]

Hi @Pbrit I do not see amounts in your posting as input, so I can not tell what should be proposal for solution.

[Pbrit]

@some_bih , The site scrumbled the table, Please find below

Using the above code you provide , what i am expecting is if I select the period Feb 2023 with the date slicer, the output for count of open SA's should be 1000.

[some_bih]

Hi @Pbrit honestly, I do not undrestand, must be me. Where are INPUT data to get desired output, how That data is connected to your initial post...

[Pbrit]

@some_bih , Please let me explain well,

From the previous solution you provide the input were below

No	Id	StartDate	EndDate	SA	Status
1	1	15/01/2023		A	Open
2	2	20/01/2023	25/01/2023	B	Open
3	3	25/01/2023		C	Open
4	4	01/02/2023	05/02/2023	D	Open
5	5	05/02/2023		E	Open
6	2	25/01/2023		B	Closed
7	4	05/02/2023		D	Closed

 and you gave me this dax which will give the distinct number of SA's when I apply open status filter on with a selected date range eg 1jan2023 to 28feb2023.

Test =

VAR __start_date = MIN('Date'[Date])

VAR _end_date = MAX('Date'[Date])

VAR __selected_status = SELECTEDVALUE(Sheet2[Status])

RETURN

CALCULATE(

    DISTINCTCOUNT(Sheet2[SA]),--calculation of distinct count for SA

    FILTER(

        'Sheet2',

        'Sheet2'[Status] = __selected_status &&

        (

            'Sheet2'[EndDate] <= _end_date &&

            'Sheet2'[StartDate] >= __start_date

        )

    )

)

What i am asking is with this code , how can I get the distinct count of all SA's that are open from  1 Jan 2022 ( assuming this the earliest date of an SA) even though I chose 1 jan2023 to 28feb2023 in my date selection.

Please if it is still not clear , is there a way we can chat using this platform or if you dont mind sharing your whats app number.

Many Thanks

[some_bih]

Hi @Pbrit Use new measure below, (row with SA = G added to test results, and in criteria fixed Status Open")

Test Status Open =

VAR __start_date = MIN('Date'[Date])

VAR _end_date = MAX('Date'[Date])

VAR __selected_status = SELECTEDVALUE(Sheet2[Status])

RETURN

CALCULATE(

    DISTINCTCOUNT(Sheet2[SA]),--calculation of distinct count for SA

    FILTER(

        'Sheet2',

        'Sheet2'[Status] = "Open" &&

        (

            'Sheet2'[EndDate] <= _end_date &&

            'Sheet2'[StartDate] >= __start_date

        )

    )

)

Output (no selection, row with SA = G added)

Output (01.01.-28.02.2023): 5

Output (13.02-1.6.07.2023): 1

[ERD]

Hi @Pbrit . You can try the measure

te1 =
VAR mindate = MIN ( 'Date'[Date] )
VAR maxdate = MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Table__[SA] ),
        Table__[StartDate] >= mindate,
        Table__[StartDate] <= maxdate,
        'Table__'[Status] = "Open"
    )

[Pbrit]

@ERD , This couldn't help achieve the result, any other response would be appreciated