Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a project table listing project id's and thier start and end dates. In this table I need to calculate a column (or measure) that tells me for each project how many days it has in each fiscal year. Then ideally I can connect this to the date table so i could show fiscal year on the axis and a count of the days remaining, (summing the days remaining for each project). Ideally it would dynamically adjust for whatever fiscal year we are in but ok with figuring that piece out later.
Level2 | StartDate | EndDate |
100057 | 11/20/2011 | 4/30/2013 |
101011 | 11/5/2015 | 11/6/2012 |
101011 | 11/5/2015 | 11/6/2012 |
101011 | 11/5/2015 | 11/6/2012 |
101011 | 11/5/2015 | 11/6/2012 |
100227 | 3/31/2019 | 4/1/2013 |
101011 | 11/5/2015 | 11/6/2012 |
101011 | 11/5/2015 | 11/6/2012 |
100227 | 3/31/2019 | 4/1/2013 |
any help is appreciated!
Hi there.
Here's something to get you started.
Model:
Calendar - Stores dates that cover all years one can find in Projects
Projects - Stores ProjectId, StartDate, EndDate
CurrentDays - Stores a selection of dates that will play the role of TODAY (slicer)
No relationships. All tables stand on their own.
Measure:
Remaining Days In Period:=
var __cutoffDate = if( HASONEVALUE( CurrentDays[CurrentDay] ), VALUES( CurrentDays[CurrentDay] ) ) var __remainingDays = SUMX( Projects, COUNTROWS( INTERSECT( VALUES( 'Calendar'[Date] ), FILTER( ALL( 'Calendar'[Date] ), 'Calendar'[Date] >= __cutoffDate && 'Calendar'[Date] >= Projects[StartDate] && 'Calendar'[Date] <= Projects[EndDate] ) ) ) ) return __remainingDays
I did this in Excel 2016, so couldn't use the latest functions from PowerBI.
Best
Darek
User | Count |
---|---|
47 | |
26 | |
22 | |
17 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |