Forum Discussion
Connecting Summarized Table to Calculated Measure
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.
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)
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 measuresactual 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
6 Replies
pls provide the sample data of two tables (not the screenshot)
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!
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 measuresactual 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
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.
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?
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)
