FTE change month over month

[Anonymous]

Dear,

I have been able to put together measure to calculate average FTEs per month and to compare evolution of FTEs month over month on totals level. However, when adding a row context such as service line, I'm struggling to modify my formula in such a way that it would work.

My data looks like this (period = month number):

Service Line	Sub Service Line	Year	Period	FTE
A	X	2020	9	10
A	Y	2020	9	15
A	Y	2020	10	20
B	R	2020	9	11
B	S	2020	9	17
B	S	2020	10	24

My current measures:

Previous Month FTE =

VAR CurrentMonth = selectedvalue( 'Rap+Performance'[Period])

VAR CurrentYear = selectedvalue( 'Rap+Performance'[Year])

VAR MaxMonthNum = calculate(max('Rap+Performance'[Period]), all('Rap+Performance'))

RETURN

IF (hasonevalue('Rap+Performance'[Period]),

sumx( filter (all ( 'Rap+Performance'), if ( CurrentMonth = 1, 'Rap+Performance'[Period] = MaxMonthNum && 'Rap+Performance'[Year] = CurrentYear - 1,

'Rap+Performance'[Period] = CurrentMonth - 1 && 'Rap+Performance'[Year] = CurrentYear)),

[FTE ]), blank())

Current Month FTE =

VAR CurrentMonth = selectedvalue( 'Rap+Performance'[Period])

VAR CurrentYear = selectedvalue( 'Rap+Performance'[Year])

VAR MaxMonthNum = calculate(max('Rap+Performance'[Period]), all('Rap+Performance'))

RETURN

IF (hasonevalue('Rap+Performance'[Period]),

sumx( filter (all ( 'Rap+Performance'), if ( CurrentMonth = 1, 'Rap+Performance'[Period] = MaxMonthNum && 'Rap+Performance'[Year] = CurrentYear,

'Rap+Performance'[Period] = CurrentMonth && 'Rap+Performance'[Year] = CurrentYear)),

[FTE ]), blank())

FTE change month over month = if (isblank([Previous Month FTE]), blank(), [Current Month FTE] - [Previous Month FTE])

However, these measures result in a table looking like this when selecting a certain period via a slicer:

Service Line	Sub SL	Previous Month FTE	Current Month FTE	FTE change month over month
A	X	53	44	-9
A	Y	53	44	-9
B	R	53	44	-9
B	S	53	44	-9
Total		53	44	-9

The totals in the above table are correct. However, I would like to see the FTEs shown correctly per Service Line and Sub Service Line, while now all the rows only show the total value ignoring this row context. Can anyone help me to modify my formulas to make this work?

Thank you!

[amitchandak]

@Anonymous , You can create a date using year and period

Date = date([year], [period],1)

Then you can  use Time intelligence with date table

examples

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
this month =MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH('Date'[Date])))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
previous month value =  CALCULATE(sum(''Table''[total hours value]),previousmonth('Date'[Date]))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Anonymous]

Dear @amitchandak , Thank you for your response! However, I don't need any year to date figures as this is about FTEs and the month over month evolution, not about sales. As mentioned, my formulas are working fine upon selecting periods so there are no issues with my dates. The only issue is to get the formula working to show the value per service line instead of per total - do you know how to modify my formula to ensure a value per row context / service line is shown?

[v-yiruan-msft · Accepted solution]

Hi @Anonymous ,

You can update the formula of measure [Current Month FTE] and [Previous Month FTE] as below:

Current Month FTE = CALCULATE(SUM('Rap+Performance'[FTE]))

Previous Month FTE = 
VAR CurrentMonth = SELECTEDVALUE( 'Rap+Performance'[Period])
VAR CurrentYear = SELECTEDVALUE( 'Rap+Performance'[Year])
VAR MaxMonthNum = calculate(max('Rap+Performance'[Period]), all('Rap+Performance'))
RETURN
IF (hasonevalue('Rap+Performance'[Period]),
sumx( filter (all ( 'Rap+Performance'), if ( CurrentMonth = 1, 'Rap+Performance'[Period] = MaxMonthNum && 'Rap+Performance'[Year] = CurrentYear - 1,
'Rap+Performance'[Period] = CurrentMonth - 1 && 'Rap+Performance'[Year] = CurrentYear)
&&'Rap+Performance'[Service Line]=MAX('Rap+Performance'[Service Line])&&'Rap+Performance'[Sub Service Line]=MAX('Rap+Performance'[Sub Service Line])),
[FTE]), blank())

Best Regards
Community Support Team _ Rena Ruan
If this post helps, then please consider Accept it as the solution to help the other members find it more.