Using Business Days as part of Formula or Measure

[dmileusinch]

Hi,

I'm trying to automatically associate the business days for a particular month into a measure that with show the headcount requirment by month in a bar chart.  I want to use the defined Business Days for each month as part of the formula to derive the headcount (tickets effort/business days) per month

Table 1: Tickets

Table 2: Business Days

Headcount = (COUNT(Tickets[Account Name])*30/60)/LOOKUPVALUE(Business Days[Business Days],Business Days[Month],1)/6.25

The formula overall works except I can't get the third value in the lookupvalue (highlighted in red) to work unless I force a month number.  I've tried to take the date/time field in the Tickets table as an input or even create a colum with the month number for each row but none of that works.

Looking for ideas.

Thanks,

[Greg_Deckler]

So because it is a measure, you would need the column in your visual and wrap the column reference with an aggregation like:

Headcount = (COUNT(Tickets[Account Name])*30/60)/LOOKUPVALUE(Business Days[Business Days],Business Days[Month],MONTH(MAX('Table'[Date])))/6.25