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
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 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