Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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])
Solved! Go to 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.
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.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
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.
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.
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
42 | |
26 | |
22 | |
13 | |
8 |
User | Count |
---|---|
75 | |
50 | |
47 | |
17 | |
17 |