Cumulative Count of Assets using Start Dateand End Date

[Anonymous]

Hi there,

I have this table "ACTIVE ASSETS": 

Asset Nr	Start Date	End Date
001	01 01 2020	20 01 2020
002	05 01 2020	30 01 2020
003	10 01 2020	15 02 2020
001	02 02 2020	25 02 2020

I would like to get the amount of active assets per day: 

COUNT ACTIVE ASSETS	Date
1	01 01 2020
2	05 01 2020
3	10 01 2020
2	20 01 2020  (-1 end of use of Asset 001)
1	30 01 2020  (-1 end of use of Asset 002)
2	02 02 2020

I have a date table Date[Date]

Appreciate any help! Thank you

[Anonymous · Accepted solution]

Hi @Anonymous ,

Please try the following formula to create a measure:

COUNT ACTIVE ASSETS =
CALCULATE (
    DISTINCTCOUNT ( 'ACTIVE ASSETS'[Asset Nr] ),
    FILTER (
        'ACTIVE ASSETS',
        'ACTIVE ASSETS'[Start Date] <= MAX ( 'Date'[Date] )
            && 'ACTIVE ASSETS'[End Date] > MAX ( 'Date'[Date] )
    )
)

 The final output is shown below:

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