YTD Calculation

[Anonymous]

Hi all,

I have previously posted about this but have now ran into another issue. I am currently trying to create a dashboard using PowerBI that calculates our employee turnover.

1. I have used the below calculation to try and get my headcount figure from raw data. The column is Month and retruns the date (1/01/2018, 1/02/2018 etc).
   
   However, what I need it to do is for January count those with date 1/01/2018 and then divide by 1 for February count those for January and February and divide by 2.. and so on.
   
   Unfortunately I can't create a summarised table as I then need to slice by team.
   
   

   2018 Average YTD Headcount = TOTALYTD(
                   COUNT('2018 Headcount'[Month]),
                   '2018 Date'[Month]
                   ) 
                   / MAX('2018 Date'[Month Number])

2.  Rolling leavers - similar issue as I need January to count and then February to add the count for Jan and Feb.

TEST 2018 Monthly Leavers running total in Month = 
CALCULATE(
	[TEST - Jan 18 YTD Perm Leavers],
	FILTER(
		ALLSELECTED('2017 Date'[Month]),
		ISONORAFTER('2017 Date'[Month], MAX('2017 Date'[Month]), DESC)
	)
)

Below is what it is returning;

When it should be:

Any ideas or thoughts would be great!!

[v-piga-msft]

Hi @Anonymous，

You could create the measure below to get your expected output.

1. The measure of Average YTD Headcount: 

Average YTD Headcount =
VAR rolling_sum =
    CALCULATE (
        SUM ( 'year Headcount'[Monthly Headcount] ),
        FILTER (
            ALL ( 'year Headcount' ),
            YEAR ( 'year Headcount'[Month] ) = YEAR ( MAX ( 'year Headcount'[Month] ) )
                && 'year Headcount'[Month] <= MAX ( 'year Headcount'[Month] )
        )
    )
VAR month_number =
    MAX ( 'year Headcount'[MonthNumber] )
RETURN
    rolling_sum / month_number

2. The measure of Rolling leavers:

TEST  Monthly Leavers running total in Month =
CALCULATE (
    SUM ( 'year Headcount'[Monthly leaves] ),
    FILTER (
        ALL ( 'year Headcount' ),
        YEAR ( 'year Headcount'[Month] ) = YEAR ( MAX ( 'year Headcount'[Month] ) )
            && 'year Headcount'[Month] <= MAX ( 'year Headcount'[Month] )
    )
)

Then you will get the output below.

Hope it can help you !

If you need additional help please share some data sample. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.)

Best Regards,

Cherry

[Anonymous]

I think I have figured it out - however, how I need to filter the Headcount by Team - all my data is linked through relationships but the YTD headcount doesn't change when I select a team through one of my slicers. I tried updating the equation to the below:

Link to sample raw data:

https://www.dropbox.com/s/c08cakt6503oh27/1801_Power%20Bi%20Data%20Sample_Turnover%20Raw%20Data.xlsx...

2 Tabs - Headcount and Leavers.

Both tabs have a month column that I need referenced..

Any help would be great because I am completely stumped.

Average YTD Headcount = 
VAR rolling_sum =
    CALCULATE (
        SUM(COUNT('2017 Headcount')),
        FILTER (
            ALL ( '2017 Headcount' ),
            YEAR ( '2017 Headcount'[Month] ) = YEAR ( MAX ( '2017 Headcount'[Month] ) )
                && '2017 Headcount'[Month] <= MAX ( '2017 Headcount'[Month] )
        )
    )
VAR month_number =
    MAX ( '2017 Headcount'[TEST Month Number 2017] )
RETURN
    rolling_sum / month_number