Forum Discussion
Solved
Sum - Headcount
Hi I'm trying to create a calculated column which gives a current headcount at the end of each month. I've managed to create a table which has the amount of starters and leavers for each month. ...
I assume you use "merge queries" with full join to create this table so that those "not match" rows will aggregate together.
In this scenario, I suggest you filter those "not match" rows in your table. You can filter empty rows in Query Editor.
If you don't want to modify the table, you can add filter expression in your calculation.
Headcount = CALCULATE( SUM(MonthlyStartersAndLeavers[Net]), FILTER ( ALL ( 'MonthlyStartersAndLeavers'[Months] ), 'MonthlyStartersAndLeavers'[Months]<>BLANK() ), FILTER(ALL(DateTable[DateKey]),DateTable[DateKey]<=MAX(DateTable[DateKey])) )
I would create a Month column, something like MONTH([MonthS]) and then edit your column so that [Net] is per row. Then, you could just create a table, place [Month] and [Net] in the table and you should get your answer.
Thanks for the response Greg_Deckler.
Your solution should work out but is there anyway to keep this within my current table?