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 th...
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.