Forum Discussion

Spencer's avatar
Spencer
Helper II
9 years ago
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. ...
Need Help
  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    Spencer

     

    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]))
)
Spencer's avatar
Spencer
Helper II
9 years ago

Alternatively I've calculated the headcount as a measure, using the below formula, but the -313 is still taken into account.

 

Headcount = CALCULATE(SUM(MonthlyStartersAndLeavers[Net]),FILTER(ALL(DateTable[DateKey]),DateTable[DateKey]<=MAX(DateTable[DateKey])))

 

  • v-sihou-msft's avatar
    v-sihou-msft
    Microsoft Employee
    9 years ago

    Spencer

     

    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]))
)
    • Spencer's avatar
      Spencer
      Helper II
      9 years ago

      That'll be ideal, huge thanks v-sihou-msft