cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Mmahachi Regular Visitor
Regular Visitor

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
Highlighted
Super User
Super User

Re: DAys remaining in each fiscal year

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
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 553 members 4,257 guests
Please welcome our newest community members: