Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Count active projects per date

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             
NED000001Released1-1-2021
NED000001Released2-1-2021
NED000001Released3-1-2021
NED000001Canceled10-1-2021
NED000002Released3-1-2021
NED000002Released4-1-2021
NED000002Released5-1-2021
NED000002Released6-1-2021
NED000002Released7-1-2021
NED000002Canceled13-1-2021


This would be the desired result.

Date                Active     
1-1-20211
2-1-20211
3-1-20212
4-1-20212
5-1-20212
6-1-20212
7-1-20212
8-1-20212
9-1-20212
10-1-20211
11-1-20211
12-1-20211
13-1-20211
14-1-20210

 

Can anyone please help me out with that?

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    Anonymous , 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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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.