Power BI: Groupby using a Calculated Table

[gavinma2022]

Hi community members, I have searched for two days but can't find anything... please help. 

The data:

• Salary table: [EmployeeCode, MonthlyUSD, DateStart, DateEnd]
• A record applies to a_date, if a_date is between DateStart and DateEnd. 
• Same EmployeeCode may have multiple record applies: ie.., given a date, one EmployeeCode could have multiple records with different MonthlyUSD.   

My intention:

• Filter the table with a date (MaxContextDate): end of each month
• Group by EmployeeCode, and Sum MonthlyUSD: get the sum USD of each employee. 
• Get the Median of [Sum Monthly USD] from the different employees 
  

The measure: 

MedianSalaryEmp =

VAR ContextDate = [MaxContextDate] //current context date

VAR TempTab = CALCULATETABLE(

Salary,

FILTER(ALL(Salary[DateStart]), Salary[DateStart] <= ContextDate ),

FILTER(ALL(Salary[DateEnd]), Salary[DateEnd] >= ContextDate || ISBLANK(Salary[DateEnd]) )

)

VAR GroupbyByTab = GROUPBY(

CALCULATETABLE(

Salary,

FILTER(ALL(Salary[DateStart]), Salary[DateStart] <= ContextDate ),

FILTER(ALL(Salary[DateEnd]), Salary[DateEnd] >= ContextDate || ISBLANK(Salary[DateEnd]) )

), [EmployeeCode], "SumUSD", SUMX(CURRENTGROUP(), [MonthlyUSD])

)

VAR Med = Median (GroupbyByTab[SumUSD])

Return Med

The error: 

Table variable 'GroupbyByTab' cannot be used in current context because a base table is expected.

Can anyone guide me how to solve this problem?

Thanks!

[amitchandak]

@gavinma2022 , refer if these approaches can help

Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM
https://community.powerbi.com/t5/Community-Blog/How-to-divide-distribute-values-between-start-date-o...

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

[gavinma2022]

Hi Amitchandak, 

Thanks for replying.. But I can't understand the solution. My problem is that I don't know how to calculate the median of the column of a temp table....