Calculation from a different table

[Diego-mx]

Hi there, I need help with a calculation that involves two tables. 

My main table has targets indexed by month, and for simplicity I want to calculate the working days;  the reference table is a Calendar table which is indexed by day, contains the month, and has another column if the day is a working day or not. 

Target:  
  - month
  - working_days ?

Calendar 
  - date
  - month 
  - is_working_num

What I have tried is to use calculate to do: 

working_days = CALCULATE( 
  sum(Calendar[is_working_num]), 
  Calendar[month] = Target[month] 
)

I get an error that the expression contains multiple columns, but I can only use one.  
Yet it seems like I'm close, but I have tried for a while with no success. 

I should also mention that there is not a relation via month-month.  This is another issue, that I might address in another time.  

Thanks. 

[v-yuezhe-msft · Accepted solution]

@Diego-mx,

Please check if one of the following columns return your expected result. For more details, please review attached PBIX file.

 Column= CALCULATE( 
  sum(Calendar[is_working_num]), 
  FILTER('Calendar','Calendar'[Monthnumber] = Target[month] 
))

working_days = SUMX(
  FILTER('Calendar','Calendar'[Monthnumber]= Target[month]),'Calendar'[is_working_num]
)

Regards,
Lydia

[MattAllington]

Read my article here.  I realise it is not identical, but it should help you. https://powerpivotpro.com/2016/12/how-many-working-days-has-an-employee-been-off-work/