PowerBI cumulative

[Dom59]

Working with Power BI, I try to analyse Employees turnover. Formula is ((Number of entered employee + number of left employee)/2) / Total number of employee in the company.

My source, is an excel file, where I have a table with :

• entry date
• left date (only if employee left). If not it stays empty. Data are registered day by day if I have an occurence. I have data starting in 2015.

The target is to get month by month for a specific year, the turnover.

The problem : When I filter for 2022 for example, the cumulative calculation I've done for the number of employee in the company only consider people entered in 2022 and not considering people entered in 2015, 2016 etc.

I've created a specific measure to count empty in left date : VDS = (COUNTBLANK('SETT'[Left Date]))

Then to cumulate, whatever is the chosen solution it doesn't works!

Someone got an idea the way I can solve this issue ?

Thanks Dom

[v-yadongf-msft · Accepted solution]

Hi @Dom59 ,

This is my test table:

Please create a new table like below:

Date = ADDCOLUMNS( CALENDAR(date(2016,01,01),date(2022,9,1)),
"Year",YEAR([Date]),
"Month",MONTH([Date]))

The model relationship between two tables:

Please try following DAX to calculate people in company:

people in company = 
CALCULATE(
    COUNTROWS('Table'),
    FILTER('Table',ISBLANK('Table'[Left date]))
)

Then create a slicer and a card visual, you can filter  how much people in company each month of 2022.

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[daXtreme]

Well, for a cumulative calculation to work, in your DAX measure that returns (Number of entered employee + number of left employee) you have to remove filter from the dates and replace it with one that spans dates from the beginning of time (in your data) to the very last date in your current range. Then you'll get what you want.

[Dom59]

Thanks for answer. I'm ok with your answer. In that case, that means when I want to know  how much people I have in my company each month of 2022 I can't filter the visualisation because part of the result comes from previous year ?

[daXtreme]

Since you do all of that in a measure you can do whatever you want in the UI. The measure manages filter contexts inside itself based on what the user have selected. It really does not matter to your measure whether part of a calculation comes from this or that part of a table. If you want to see how such calculations are built and how they (should) work, please see this for instance: Computing running totals in DAX - SQLBI