Count of items active on date over time

[Anonymous]

I have a table that looks like the following but contains 200,00 rows:

Item ID, Created Date, Resolved Date

1234, 01/04/21, 01/20/21

5678, 01/03/21, 02/01/21

I would like to create a time series where on any give date I can count th number of records that meet the following criteria; created date <= time series date <= resolved date to return results as follows:

Date, Count

01/01/21, 0

01/02/21, 0

01/03/21, 1

01/04/21, 2

etc

This will allow me to create a chart over time to show how many items were active on a given date.

[v-luwang-msft]

Hi @Anonymous 

The solution  amitchandak  provide is right.And You could try the following steps!

Step1,create date table:

Dates = CALENDAR("2021/1/1","2021/12/31")

Step2,use the following measure on date table:

Measure 2 =
VAR test2 =
    MAXX ( ALLSELECTED ( Dates[Date] ), 'Dates'[Date] )
VAR test3 =
    MINX ( ALLSELECTED ( Dates[Date] ), 'Dates'[Date] )
VAR test1 =
    CALCULATE (
        COUNT ( 'Table'[Item Id] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[created date] <= test2
                && 'Table'[resolved date] >= test3
        )
    )
VAR test4 =
    IF ( test1 = BLANK (), 0, test1 )
RETURN
    test4

Final you will see the below:

Click here to download pbix if you need!

Wish  it is helpful for you!

Best Regard

Lucien Wang

[amitchandak · Accepted solution]

@Anonymous , A measure like this with an independent date table

Count =
var _max = maxx(allselected([Date]), 'Date'[Date])
var _min = minx(allselected([Date]), 'Date'[Date])
return
calculate(count(Table[Item Id]), filter(Table, Table[created date] <=_max && Table[resolved date] >=Max))

or refer this blog

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

[Anonymous]

This was perfect, thank you! I'll mark this as the solution. I did have one more question. If I had an additional column in my data, for your example say department, and I wanted to filter by that for any of these specific measures what's the best way to do that? I don't want to do a filter on the entire visual, just the measure itself.