Help in expression (put computed column value in set analysis)

[ali_hijazi]

Hello got a pivot table with Year, Quarter, and month as dimensions

I managed using the FirstSortedValue function to get the latest month per month, or per quarter or per year
Now what I want is to use this value in my expression to get the headcount 

 
when all columns are expanded I get the max month per month which is fine

when I collapse the Quarter column I get the latest month as per the data and this is fine

and when I collapse the year column I get the latest month in the year and this is fine
Now what I want is to count(distinct Employee) where monthYear is the one I'm getting using the FirstSortedValue
so when the year is collapsed I should get 1 since in 201905 the distinct count of employee is 1
check attached sample data and help please @rwunderlich , @sunny_talwar 

[sunny_talwar · Accepted solution]

Try this

Pick(Dimensionality(),
	Sum(Aggr(If(YEARMONTH = Max(TOTAL <YEAR> YEARMONTH), Count(DISTINCT EMPLOYEE)), YEAR, Quarter, YEARMONTH)),
	Sum(Aggr(If(YEARMONTH = Max(TOTAL <YEAR, Quarter> YEARMONTH), Count(DISTINCT EMPLOYEE)), YEAR, Quarter, YEARMONTH)),
	Count(DISTINCT EMPLOYEE)
)