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
kalspiros
Helper I
Helper I

Measure for MAX dates

Hello everybody.

 

Just finished reading the Definitive Guide to DAX therefore, I feel rather humilated posing this question.

I think it's a super easy one but I'm just out of time with that report thus, thought of asking.

 

A table with time off records. Each [User] has several time off records.

Each time off record has an [End] date for the last date of the leave and an [Available] number of remaining accrued hours.

There are certain records that have no [Available] number (records of unpaid leave that do not affect the accrued hours)

The table also contains other data such as [Manager] and [Department].

 

I would like a measure that will show the latest [Available] record per employee.

It will have to filter out the records with no [Available] data.

It needs to be an average because once collapsing any additional fields, we will need the average available hours per, say, [Manager] and/or [Department].

Currently this is where I stand, it works well in Employee granualarity but when collapsing to Manager, the average makes no sense.

 

 

=VAR tablStart=CALCULATETABLE(
		'Time Off',NOT(
			ISBLANK(
				'Time Off'[Available]
				)
			),
			LASTDATE(
			'Time Off'[End]
			)
		)
VAR tabl=CALCULATETABLE(
		tablStart,ALLEXCEPT(
			'Time Off','Time Off'[Manager Name]
			)
		)
VAR LatestRemain=CALCULATE(
			AVERAGEX(
				tabl,'Time Off'[Available]
				)
			)
RETURN
IF(
	LatestRemain=0,BLANK(),IF(
		LatestRemain<0,MROUND(
			LatestRemain,-0.5
		),MROUND(
			LatestRemain,0.5
		)
	)
)

 

My bets of naughtiness for ALLEXCEPT...

 

What do you think?

 

Many thanks in advance

1 ACCEPTED SOLUTION
kalspiros
Helper I
Helper I

Done!

measure 1:=VAR tablFirst=SUMMARIZE('Time Off','Time Off'[Person])
VAR tabl=ADDCOLUMNS(tablFirst,"lastAvailable", CALCULATE(SUM('Time Off'[Available]),LASTDATE('Time Off'[End])))
VAR LatestRemain=CALCULATE(AVERAGEX(tabl, [lastAvailable]))
RETURN
IF(LatestRemain=0,BLANK(),IF(LatestRemain<0,MROUND(LatestRemain,-0.5),MROUND(LatestRemain,0.5)))

View solution in original post

1 REPLY 1
kalspiros
Helper I
Helper I

Done!

measure 1:=VAR tablFirst=SUMMARIZE('Time Off','Time Off'[Person])
VAR tabl=ADDCOLUMNS(tablFirst,"lastAvailable", CALCULATE(SUM('Time Off'[Available]),LASTDATE('Time Off'[End])))
VAR LatestRemain=CALCULATE(AVERAGEX(tabl, [lastAvailable]))
RETURN
IF(LatestRemain=0,BLANK(),IF(LatestRemain<0,MROUND(LatestRemain,-0.5),MROUND(LatestRemain,0.5)))

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.