topic Calculate the count of Open status using date selection filter with DAX in DAX Commands and Tips

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

Pbrit — Tue, 05 Sep 2023 12:41:05 GMT

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

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

ERD — Tue, 05 Sep 2023 18:14:12 GMT

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" )

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

Pbrit — Tue, 05 Sep 2023 21:44:09 GMT

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

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

Pbrit — Mon, 11 Sep 2023 11:46:24 GMT

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

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

some_bih — Mon, 11 Sep 2023 13:07:36 GMT

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!

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

Pbrit — Mon, 11 Sep 2023 14:26:41 GMT

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.

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

some_bih — Mon, 11 Sep 2023 14:31:27 GMT

Hi Pbrit to reduce retesting please provide expected outpu for input

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

Pbrit — Mon, 11 Sep 2023 14:53:37 GMT

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

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

some_bih — Tue, 12 Sep 2023 05:34:01 GMT

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

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

Pbrit — Tue, 12 Sep 2023 15:06:50 GMT

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.

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

some_bih — Tue, 12 Sep 2023 16:29:16 GMT

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...

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

Pbrit — Tue, 12 Sep 2023 17:01:55 GMT

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

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

some_bih — Wed, 13 Sep 2023 06:21:42 GMT

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