Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
martti
Helper I
Helper I

Count measure to take time into account

I have a measure that counts users from a table that also has dates when they arrived or left, e.g.

UsersStarting dateLeaving date
User A01.01.201930.08.2019
User B01.08.2019 
User C01.07.201931.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?

1 ACCEPTED SOLUTION
dax
Community Support
Community Support

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.

View solution in original post

6 REPLIES 6
Anonymous
Not applicable

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.

Anonymous
Not applicable

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. 

powerbi_abi.PNG

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!

dax
Community Support
Community Support

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.

Yes! The measure 4 works exactly as I hoped, without the need for an extra table. Thank you!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.