topic Count of rows based on start date and end date column in DAX Commands and Tips

Count of rows based on start date and end date column

Vaibhav3008 — Thu, 05 Oct 2023 12:14:48 GMT

Hi All,

I have a table like below

I need to count the number of app ids which fall in each month (e.g. how many app ids are there in oct 2023, how many in nov 2023 and so on).

After that I need to display it on a stacked bar chart with functional group as legend. months as x axis and count of app ids as y axis.

Any help would be appreciated.

Thanks

Re: Count of rows based on start date and end date column

BeaBF — Thu, 05 Oct 2023 17:36:50 GMT

Vaibhav3008 Hi!

Can you paste the data?

BBF

Re: Count of rows based on start date and end date column

Vaibhav3008 — Thu, 05 Oct 2023 17:42:25 GMT

Thanks for the reply. Here it is:

Functional Group	App Id	Start Date	End Date
FG01	App01	19-06-2023	27-06-2023
FG02	App02	19-06-2023	27-06-2023
FG03	App03	08-06-2023	29-06-2023
FG01	App04	23-05-2023	31-08-2023
FG02	App05	16-11-2023	13-12-2023
FG03	App06	01-11-2023	01-12-2023
FG01	App07	22-02-2024	20-03-2024
FG02	App08	22-02-2024	20-03-2024
FG03	App09	26-07-2024	05-09-2024
FG01	App10	22-11-2024	19-12-2024
FG02	App11	22-04-2024	15-06-2024
FG03	App12	20-02-2025	19-03-2025

Re: Count of rows based on start date and end date column

BeaBF — Thu, 05 Oct 2023 17:50:28 GMT

Vaibhav3008 Hi!
you need to create two measures, you can find the solution in the attached pbix
If I answered correctly, accept my answer as a solution, otherwise direct the problem to me and we will solve it!

BBF

Re: Count of rows based on start date and end date column

Vaibhav3008 — Thu, 05 Oct 2023 18:04:18 GMT

Thanks for the all the help on this.

Currently the count is only taking start date into account. I need the count of apps falling in between start and end dates. Example - App11 should be counted in April 2024, May 2024 and June 2024 because the start and end dates are like that. Another example - App05 should be counted in November 2024 and December 2024.

Re: Count of rows based on start date and end date column

BeaBF — Thu, 05 Oct 2023 18:43:56 GMT

Vaibhav3008 Sorry!

Try with this new,

BBF

Re: Count of rows based on start date and end date column

Vaibhav3008 — Fri, 06 Oct 2023 04:46:55 GMT

Thanks for the reply.

I am actually doing the same thing which you did here. But somehow it's not working.

As you can see in the pic below. The count for June 2023 should be atleast 4 (App01, App02, App03 and App04 all are falling inside June 2023). But the count in graph is coming as 1 only for June 2023.

Re: Count of rows based on start date and end date column

BeaBF — Fri, 06 Oct 2023 07:16:53 GMT

Vaibhav3008 right, here the code adjusted

BBF

Re: Count of rows based on start date and end date column

Anonymous — Fri, 06 Oct 2023 07:45:30 GMT

Hi @Vaibhav3008,

Based on your description, I make the new DAX expression, you may check the result below:

App Count = 
var _selyear=SELECTEDVALUE('DateTable'[Date].[Year])
var _selmonth=SELECTEDVALUE('DateTable'[Date].[MonthNo])
var _date1= EOMONTH(date(_selyear,_selmonth,1),0)
var _date2= date(_selyear,_selmonth,1)
return
COUNTROWS(
    FILTER(
        'Table',
        'Table'[Start Date] <=_date1 &&
        'Table'[End Date] >=  _date2
    )

)

Hope it helps.

Best regards,

Community Support Team_ Scott Chang

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.