Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
I have a measure that counts users from a table that also has dates when they arrived or left, e.g.
Users | Starting date | Leaving date |
User A | 01.01.2019 | 30.08.2019 |
User B | 01.08.2019 | |
User C | 01.07.2019 | 31.10.2019 |
User B is still working at the company. I need it to count the users in the time frame that they were working in, so measure UserCount on a visual with months on the x-axis would give result of 1 from January to June, 2 in July, 3 in August, 2 in September, October, and 1 in November. So far I've just been using different columns for months when users have worked and counted those, but that solution seems crass. I cannot create a relationship with the date dimension since it would introduce ambiguity between other data included in the visual.
Can someone point me in the right direction?
Solved! Go to Solution.
Hi martti,
You could refer to my sample to see whether it work or not
Best Regards,
Zoe Zhi
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
If my assumption is correct, you are tyring to do count using the between Starting date and Leaving date.
Try modifying the query for the Leaving date i.e. replace the Leaving date with
IF( ISBLANK(TABLE[Leaving Date]), "31.12.2020",TABLE[Leaving Date])
Hi there!
That's correct, I'm trying to do count of Users column between starting date and Leaving date.
But the blanks in the leaving date column aren't really the problem, it's that I can't make the measure count the users based on the time dimension.
Could you post what is the error you message you are getting?
And also the Model(picture), just to get more understanding on your issue.
I wasn't getting an error message, but trying to find a better way to achieve the result of counting the users who worked during the specific times.
The orange line is the COUNT(Users) without accounting for when they worked there. The purple line shows what it is supposed to be.
I did find a solution though. I was being dumb and didn't consider that I could just make a separate table for the count of people based on the raw data that I could connect to the date dimension. But thanks for thinking along!
Yes! The measure 4 works exactly as I hoped, without the need for an extra table. Thank you!
User | Count |
---|---|
128 | |
108 | |
99 | |
65 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |