Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 ...
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    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.

amitchandak's avatar
amitchandak
Super User
4 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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.