topic Re: DAys remaining in each fiscal year in DAX Commands and Tips

DAys remaining in each fiscal year

Mmahachi — Wed, 26 Jun 2019 15:08:16 GMT

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!

Re: DAys remaining in each fiscal year

Anonymous — Wed, 26 Jun 2019 18:09:15 GMT

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