count active elements items between 2 dates

[BigLo]

Hello,

I want to count the number of active elements between 2 dates. I have their activity start date and their activity end date.

My goal is to make a line graph with the number of active elements per day. 

I must be able to integrate slicers on the type, cat, ...

A sample of data.

For example, 2021-02-12 (1 item) and 2021-02-13 (2 items)

[Anonymous]

Hi @BigLo ，

I created a sample pbix(see attachment) for you, please check whether that is what you want.

1. Create a date dimensio table

Date = CALENDAR(MIN('Table'[Start]),MAX('Table'[End]))

2. Create a measure to get the count of the active elements

Active items = 
CALCULATE (
    DISTINCTCOUNT ( 'Table'[Id] ),
    FILTER (
        'Table',
        'Table'[Start] >= SELECTEDVALUE ( 'Date'[Date] )
            && 'Table'[End] >= SELECTEDVALUE ( 'Date'[Date] )
    )
)

3. Create a line chart( Axis: Date(From date dimension table)  Values: Measure)

Best Regards

[BigLo]

I think it is not correct or my explanation was not clear. 

In the Beginning, all items are active. 

I changed the comparison sign and everything is OK. so thanks, I never found without your help.

[Anonymous · Accepted solution]

Hi @BigLo ,

I'm so sorry that I made a mistake on the formula of measure. Is there anything else about this thread need help from my side? If it is all ok now, could you please mark it Answered now? It will help the others in the community find the solution easily if they face the same problem with you. Thank you.

Best Regards

[Pragati11]

Hi @BigLo ,

Few questions:

• what is the definition of ACTIVE elements between 2 dates? you haven't mentioned it.
• The screesnhot of the data doesn't help us if we want to try it at our end. Please attach sample data as a file attachment so that it can easily be imported to Power BI.

Thanks,

Pragati

[BigLo]

Thanks,

Here is the file https://1drv.ms/x/s!AnCIbCo-paOMh0xCJDd-hfe73cfL?e=EHTCoO 

an item is active on the start date until the end date.
For example: the active start of id 1 is 2021-02-01 and its active end is 2022-07-03.