Count number of actual project per year to present

[mic_rys]

hi, i need a dax hero! 🙂
I need to count number of actual projects peer year to present.

If project has EndYear - should be counted between start and end year
sample of model:

ID	StartYear	EndYear
Proj1	2005	2007
Proj2	2005
Proj3	2010	2012
Proj4	2010
Proj5	2007	2008

The resuld should be:

Year        ActualProj

2005	2		proj1,2
2006	2		proj1,2
2007	3		proj1,2,3
2008	2		proj2,5
2009	1		proj2
2010	3		proj2,3,4
2011	3		proj2,3,4
2012	3		proj2,3,4
2013	2		proj2,4
…	2		proj2,4
2021	2		proj2,4

I tried something like this but it doesnt work properly:

ActualProj =
var toPresent =
CALCULATETABLE(
CALCULATETABLE(Projects,ISBLANK(Projects[StartYear]) = FALSE() && ISBLANK(Projects[EndYear]) = TRUE()),
FILTER(ALL(Date[Year]), Projects[StartYear] <=MAX(Date[Year]))
)
var toEndDate =
CALCULATETABLE(
CALCULATETABLE(Projects,ISBLANK(Projects[StartYear]) = FALSE() && ISBLANK(Projects[EndYear]) = FALSE()),
FILTER(ALL(Projects[Year]), Projects[StartYear] <=MAX(Projects[EndYear]))
)

return
COUNTX(UNION(toPresent,toEndDate),Projects[ID])

[Jihwan_Kim · Accepted solution]

Hi, @mic_rys 

Please check the below picture and the sample pbix file's link down below, whether it is what you are looking for.

The first step is to create a disconnected year table.

Projects Count =

CALCULATE (

COUNTROWS ( Projects ),

FILTER (

Projects,

Projects[StartYear] <= MAX ( Years[Year] )

&& OR ( Projects[EndYear] >= MIN ( Years[Year] ), Projects[EndYear] = BLANK () )

)

)

Project ID describe =

CALCULATE (

CONCATENATEX( Projects, Projects[ID],", "),

FILTER (

Projects,

Projects[StartYear] <= MAX ( Years[Year] )

&& OR ( Projects[EndYear] >= MIN ( Years[Year] ), Projects[EndYear] = BLANK () )

)

)

https://www.dropbox.com/s/7olpksz7j9pzeua/mic%20rys%202.pbix?dl=0 

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

[mic_rys]

@Jihwan_Kim You are always there for us! Thanks a lot!