The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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)
you can create a date time in table 1
Proud to be a Super User!
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)
pls provide the sample data of two tables (not the screenshot)
Proud to be a Super User!
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
Proud to be a Super User!
User | Count |
---|---|
80 | |
74 | |
41 | |
30 | |
28 |
User | Count |
---|---|
107 | |
96 | |
53 | |
47 | |
47 |