Forum Discussion
Resolver IIIDifference to last month using disconnected time table
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:
Is there a way to count that?
Here is the link for the sample pbix file: https://filetransfer.io/data-package/rEoww2op#link
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.
2 Replies
johnt75Super User
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.
