Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
Solved! Go to 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.
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.
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
126 | |
78 | |
77 | |
60 | |
52 |
User | Count |
---|---|
165 | |
86 | |
68 | |
68 | |
58 |