Forum Discussion
Count values between dates
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?
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.
4 Replies
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
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.
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.
Good night v-zhangti !
This solution works perfectly and solved the problem.Thanks a lot for the help!
