Count active projects per date

[Emilio220]

Hi all,

I am struggling with the following: I would like to count the number of active projects per date. For this I want to create an extra column next to the date column (see table 2) to show the number of active projects per date in a visual.

This is the data I'm working with:

Project	Status	Date
NED000001	Released	1-1-2021
NED000001	Released	2-1-2021
NED000001	Released	3-1-2021
NED000001	Canceled	10-1-2021
NED000002	Released	3-1-2021
NED000002	Released	4-1-2021
NED000002	Released	5-1-2021
NED000002	Released	6-1-2021
NED000002	Released	7-1-2021
NED000002	Canceled	13-1-2021

This would be the desired result.

Date	Active
1-1-2021	1
2-1-2021	1
3-1-2021	2
4-1-2021	2
5-1-2021	2
6-1-2021	2
7-1-2021	2
8-1-2021	2
9-1-2021	2
10-1-2021	1
11-1-2021	1
12-1-2021	1
13-1-2021	1
14-1-2021	0

Can anyone please help me out with that?

[amitchandak]

@Emilio220 , Create an independent date table and use date from date table in visual

try a measure like

calculate(distinctcount(Table[Project]), filter(Table, Date[Date] <= max(Table[Date]) && Table[Status] ="Released")) -
calculate(distinctcount(Table[Project]), filter(Table, Date[Date] <= max(Table[Date]) && Table[Status] ="Canceled"))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Emilio220]

@amitchandak,

Thank you for your quick reply.

I have tried the meassure and got the following result: 

 
However, the project becomes active from the first release date until the canceled date. With this solution it only counts the rows with the dates. Sorry if my explanation was unclear.