Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mmahachi
Advocate I
Advocate I

DAys remaining in each fiscal year

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. 

 

Level2StartDateEndDate
10005711/20/20114/30/2013
10101111/5/201511/6/2012
10101111/5/201511/6/2012
10101111/5/201511/6/2012
10101111/5/201511/6/2012
1002273/31/20194/1/2013
10101111/5/201511/6/2012
10101111/5/201511/6/2012
1002273/31/20194/1/2013

 

any help is appreciated!

1 REPLY 1
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors