Forum Discussion
Solved
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. Meanwh...
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
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)