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 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]))
)
That'll be ideal, huge thanks v-sihou-msft