Need help with measure for % change from previous month

[RichOB]

Hi, I'm looking for help with a measure that will show the monthly % change from the previous month. For example, from the table data, I want the measure to show:

January (4 new starters) = I'm guessing this will be a 0 as it's the starting point?

February (3 new starters) = -25%

March (6 new starters) = 100%

Location	Date	New Employees
London	01/01/2024	1
London	01/01/2024	1
London	01/01/2024	1
London	01/01/2024	1
London	01/02/2024	1
London	01/02/2024	1
London	01/02/2024	1
London	01/03/2024	1
London	01/03/2024	1
London	01/03/2024	1
London	01/03/2024	1
London	01/03/2024	1
London	01/03/2024	1

Thanks!

[Anonymous · Accepted solution]

Hi, drx .thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.

Hello,@RichOB .I am glad to help you.

I hope my test below helps you.

If the date in your real data is fixed to the beginning of each month (the first of each month)
then you can write a measure based on the change in date.

Proportion of monthly increments = 
VAR _monthCount=CALCULATE(COUNT('Table'[New Employees]),ALLEXCEPT('Table','Table'[Date]))
VAR _date =MAX('Table'[Date])
VAR _previousDate=EOMONTH(_date,-2)+1
VAR _previousToThis=CALCULATE(COUNT('Table'[New Employees]),FILTER(ALL('Table'),'Table'[Date]=_previousDate))
VAR _differ= _monthCount-_previousToThis
VAR _NumPercent=DIVIDE(_differ,_previousToThis)
RETURN
IF(ISBLANK(_NumPercent),
0,
_NumPercent)

If your dates are not fixed (fixed to the first day of the month), I recommend that you start by grouping by date.

Sort and group the dates by power query to generate an index column.

1. Group all rows according to the date column.

create index column

3. Expand the table

Calculated based on the change in the index column measure
index-1 is the previous date to the current date

M_index = 
VAR _monthCount=CALCULATE(COUNT('Table'[New Employees]),ALLEXCEPT('Table','Table'[Date]))
VAR _index=MAX('Table'[Index])
VAR _previousIndex=_index -1
VAR _previousToThis=CALCULATE(COUNT('Table'[New Employees]),FILTER(ALL('Table'),'Table'[Index]=_previousIndex))
VAR _differ= _monthCount - _previousToThis
VAR _NumPercent=DIVIDE(_differ,_previousToThis)
RETURN
IF(ISBLANK(_NumPercent),
0,
_NumPercent)

Here is my test data:
 

Date	Location	New Employees	Index
Monday, January 1, 2024	London	1	1
Monday, January 1, 2024	London	1	1
Monday, January 1, 2024	London	1	1
Monday, January 1, 2024	London	1	1
Thursday, February 1, 2024	London	1	2
Thursday, February 1, 2024	London	1	2
Thursday, February 1, 2024	London	1	2
Friday, March 1, 2024	London	1	3
Friday, March 1, 2024	London	1	3
Friday, March 1, 2024	London	1	3
Friday, March 1, 2024	London	1	3
Friday, March 1, 2024	London	1	3
Friday, March 1, 2024	London	1	3

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[drx]

wrap the date add in a 
Calculate(
sum(new employees),
Filter(
[table that contains date],
DATEADD([Date],-1,Month)

[drx]

You could do something like
sum(new employees)
/
Calculate(
sum(new employees),
DATEADD([Date],-1,Month)
-1

if its a kpi in the first half of the sum you would use calculate and filter the date with month([date collumn]) = Month(TODAY())
but if its for the tabel you can leave it like above and remove the date collumn and replace by a month one 

this is my first time answering and im pretty new to BI so i might be wrong

[RichOB]

Hi @drx thanks for your reply.

Unfortunately it's giving me the error message:

"A function 'DATEADD' has been used in a True/False expression that is used as a table filter expression. This is not allowed."