cancel
Showing results for
Did you mean:
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.

 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!

Highlighted
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

Announcements

#### Challenge: Can You Solve These?

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

#### Community News & Announcements

Get your latest community news and announcements.

#### 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.

#### 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
Recent signins: