Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Count of Active Projects between two dates

My apologies if this exists already, i have been searching and found similar, but nothing that has worked when tested out, nor seems to fit this need.   I have a table with a Project ID, Start Date...
Need Help
v-lili6-msft's avatar
v-lili6-msft
Community Support
8 years ago

Hi@ TO_CB

After my research, you can do these follow my steps like below:

Step 1:

Add a date table like below:

Step 2:

Cross join date table and project table

Table = FILTER(CROSSJOIN(Table1,'Calendar'),'Calendar'[Date]>=Table1[StartDate]&&'Calendar'[Date]<=(IF(Table1[EndDate] <>BLANK(),Table1[EndDate],MAX('Calendar'[Date]))))

Step 3:

Add the measure:

Measure = DISTINCTCOUNT('Table'[Project])

Result:

Here is demo , please try it

https://www.dropbox.com/s/gm154bq6hc7op0j/Count%20of%20Active%20Projects%20between%20two%20dates.pbix?dl=0

 

 

 

Best Regards,

Lin