Connecting Summarized Table to Calculated Measure

[kfordo]

I'm working to try to calculate the difference between a projection and an actual table. In the projection table (visual below) I have a summarized table that has projected attrition by month. Meanwhile my actual counts comes from an unsummarized table where I have a measure that counts attrition by counting rows of employee exits. 

Where I am stuck is how do I connect the table so that I can subtract the actuals measure from the projection number below. 

IE. In the projections table I have 14 projected attrition in the actual count I have 12. I want a calculation that tells me the difference is 2.

[mickey64 · Accepted solution]

Please add a 'Date' column and add some relationships.

In this formurla '1' means the first day of month.

You may use '2'-'28' insted of '1'.

When calculating by month, there is no problem in specifying a fixed value for the day.

Date = DATE([Year],[Month],1)

[mickey64]

I think if you add a calendar table and set up 2 relationships between the two data tables and the calendar table you can create a subtraction formula.

[kfordo]

The issue I'm encountering is that the projections table is only by month and the employee data table is by date. 

Is there a way to connect both to the calendar table?

[mickey64 · Accepted solution]

Please add a 'Date' column and add some relationships.

In this formurla '1' means the first day of month.

You may use '2'-'28' insted of '1'.

When calculating by month, there is no problem in specifying a fixed value for the day.

Date = DATE([Year],[Month],1)

[ryan_mayu]

pls provide the sample data of two tables (not the screenshot) 

[kfordo]

So here are example tables and an example of the output I'm looking for. I think what may be the issue is how I'm connecting the projections table to the calendar table since projection table is only by month. 

Thank you for any help!

[ryan_mayu · Accepted solution]

@kfordo 

you can create a date time in table 1

date = date('Table 1'[Year],'Table 1'[Month],1)

then you can build relationship between table 1 and dim time table.

then you can create measures

actual exits = countx(FILTER(all('Table 2'),year('Table 2'[Termination Date])=max('Table 1'[Year])&&month('Table 2'[Termination Date])=max('Table 1'[Month])),'Table 2'[Termination Date])

difference = [actual exits]-sum('Table 1'[Projected Exits])

pls see the attachment below