topic Count values between dates in DAX Commands and Tips

Count values between dates

Anonymous — Fri, 12 Aug 2022 02:00:45 GMT

Hi all!

I need a DAX measure to count items between dates, example:

item	start	end
A	01/01/2022	03/01/2022
B	05/01/2022	10/01/2022
C	04/01/2022	06/01/2022
D	02/01/2022	08/01/2022
E	07/01/2022	09/01/2022

Result with DAX:

calendar[date]	quantity item	ref item
01/01/2022	1	A
02/01/2022	2	A, D
03/01/2022	2	A, D
04/01/2022	2	C, D
05/01/2022	3	B, C, D
06/01/2022	3	B, C, D
07/01/2022	3	B, D, E
08/01/2022	3	B, D, E
09/01/2022	2	B, E
10/01/2022	1	B

Obs: column "ref item" in second table only illustrative, not consider

Can you help?

Re: Count values between dates

amitchandak — Fri, 12 Aug 2022 03:07:15 GMT

Anonymous , first expand between dates in power query

https://amitchandak.medium.com/power-query-get-all-dates-between-the-start-and-end-date-9ad6a84cf5f2

Then you can concat in power query of in DAX

Concatenate Text- Measure, DAX Table, and Power Query Table: https://youtu.be/xAh3tz1qo24

The first step can be done in DAX too , and a measure with help from cancatenatex

https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-or-end-date/ba-p/1503785

Re: Count values between dates

v-zhangti — Wed, 17 Aug 2022 05:30:12 GMT

Hi, Anonymous

You can try the following methods.

Table:

calendar = CALENDAR(MIN('Table'[start]),MAX('Table'[end]))

Measure:

quantity item = CALCULATE ( COUNT ( 'Table'[item] ), FILTER ( ALL ( 'Table' ), [start] <= SELECTEDVALUE ( 'calendar'[Date] ) && [end] >= SELECTEDVALUE ( 'calendar'[Date] ) ) )

Is this the result you expect?

Best Regards,

Community Support Team _Charlotte

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

Re: Count values between dates

Anonymous — Thu, 18 Aug 2022 01:13:23 GMT

amitchandakthanks for replying!

This solution works perfectly but it expands the table according to the date range.

When you have a lot of data, the load becomes very heavy.

Re: Count values between dates

Anonymous — Thu, 18 Aug 2022 01:14:58 GMT

Good night v-zhangti !
This solution works perfectly and solved the problem.

Thanks a lot for the help!