Calculating MoM % Change using measures

[Joymuststudy]

We are trying to calculate the Month-over-month employee type % change in Power BI, spent a few days but still no luck, anyone can help?

Raw data:

We have the following raw data table for each month (Jan, Feb, Mar, etc..), the Manager, Employee and Employee Type columns will change every month.

Department	Manager	Employee Name	Employee Type
Marketing	A	Jack	Full-Time
Marketing	A	Jims	Contractor
Marketing	A	John	Full-Time
Marketing	B	Alice	Full-Time
Marketing	B	Andrew	Outsource
HR	A	Celine	Full-Time
HR	A	Candy	Full-Time
HR	D	Catherine	Full-Time
HR	D	Cathy	Full-Time
HR	D	Cat	Full-Time
HR	E	Caca	Full-Time
HR	F	Cici	Contractor
HR	G	Apple	Contractor
Production	H	Fufu	Contractor
Production	I	Floral	Outsource
Production	J	Funny	Outsource
Tech	K	Fan	Outsource
Tech	L	Gina	Outsource
Tech	M	Gigi	Outsource
Tech	M	Gugu	Full-Time
Tech	M	Gogo	Contractor
Marketing	A	Mimi	Outsource
HR	A	Mumu	Full-Time
HR	D	Mama	Full-Time
HR	E	Mmmm	Full-Time
HR	F	Jaja	Contractor
HR	G	Juju	Contractor
Production	H	Jojo	Contractor
Production	I	Jiji	Outsource
Production	J	Wuwu	Outsource
Tech	K	Wawa	Outsource
Tech	L	Wiwi	Outsource
Tech	M	Wwww	Outsource
Tech	M	Qiqi	Full-Time
Tech	M	Ququ	Contractor

What we have now:

a table (visualization) showing the total # of employees by employee type(rows) and by months (columns)

	Jan	Jan	Feb	Feb
Employee Type	#	%	#	%
Full-time
Contractor
Outsource

We also have a slicer for Manager and Department, right now numbers in the above table will change if we change our selection in the slicer.

Questions:

How do we get MoM % change by each category(employee type)? and we want the result dynamic (results change if we change selection in slicer - Manager and Department)

	Jan	Jan	Feb	Feb
Employee Type	#	%	#	%
Full-time		*		*
Contractor		*		*
Outsource		*		*

What we tried before (but doesnt work):

create a summarized table each each unique (employee type+department+manager), but it doesnt work because sum of % change in each cateory doesnt equal to the overall % change.

Any help will be appreciated! thanks : )

[v-shex-msft]

Hi @Joymuststudy,

Can please share some dummy data with a similar data structure and expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[amitchandak]

@Joymuststudy , I am not seeing any date and month column. In any case, I would like to have date column, or convert month to: month start or end date.

And then will try to use time intelligence

example

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]))

diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])

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.