Difference to last month using disconnected time table

[Saap]

Hi all,

In my fact table I have 3 columns: [Employee], [ContractStart] and [ContractStop].
I also have a disconnected time table.

Using dates from disconnected time table and below measure I count the number of employees with active contract in every month.

ACTIVE =

CALCULATE( DISTINCTCOUNT( Employees[Employee] ),

    FILTER( VALUES( Employees[ContractStart] ), Employees[ContractStart] <= MAX( Dates[Date] ) ),

    FILTER( VALUES( Employees[ContractStop] ), OR( Employees[ContractStop] >= MIN( Dates[Date] ), ISBLANK( Employees[ContractStop] ) ) ) )

I would like to count the difference in the number of active employees to last month:

YYYY-MM	ACTIVE	DESIRED OUTCOME LAST MONTH	DESIRED OUTCOME DIFFERENCE
2020-10	1		1
2020-11	24	1	23
2020-12	59	24	35
2021-01	87	59	28
2021-02	85	87	-2
2021-03	75	85	-10
2021-04	76	75	1
2021-05	68	76	-8
2021-06		68	-68

I tried using the CALCULATE with PREVIOUSMONTH function but it doesn't work:

ACTIVE LM = CALCULATE( [ACTIVE], PREVIOUSMONTH( Dates[Date] ))

Is there a way to count that?

Here is the link for the sample pbix file: https://filetransfer.io/data-package/rEoww2op#link

[johnt75 · Accepted solution]

Right click on your Date table and choose "Mark as date table". Time intelligence functions like PREVIOUSMONTH need a proper date table.

Nothing else needs changing, you don't need a relationship from Date to Employees and the measure you have works fine.

[Saap]

Hi@johnt75 

Thanks, that works perfectly.
I didn't think that marking date table as a date table would solve this problem.
In my other reports I didn't mark the date table and PREVIOUSMONTH worked anyway so I thought the problem lies somewhere else.