Totals Days in enrolled

DAX DAX Calculations
1

Hello,

I have a table with the building name, user ID, their enter date, and leave date. I have another date table which I used to create a column in the table that has the user ID to calculate the number of days that they have been a member. However, I am trying to show the number of days that a user has been a member on a monthly basis.
So it would be a matrix table with the building name on the row. the months in the column the measure in the value. How should I pull in the month here? I tried to create an inactive relationship between the user table and date table but that didn’t go as planned either.

Attaching sample data below.

Building Name User ID Enter Date Leave Date
Test site 1 101 3/2/2021
Test site 1 102 5/3/2021 5/20/2021
Test site 1 103 6/5/2021
Test site 2 104 2/2/2021
Test site 2 105 1/1/2021
Test site 2 106 1/3/2021 8/1/2021
Test site 3 107 8/8/2021

Any ideas or suggestions would be greatly appreciated.

2

@supergallagher25 ,

If you can please provide your PBIX work in progress, or a representative version if there’s sensitive data in your file, I feel like this one should be pretty straightforward to work through. Thanks!

  • Brian
3

Hello @supergallagher25,

Thank You for posting your query onto the Forum.

Can you try the measure provided below?

Total Days = 
DATEDIFF( 
    SELECTEDVALUE( TableName[Enter Date] ) ,
    SELECTEDVALUE( TableName[Leave Date] ) ,
    DAY )

Hoping you find this useful and meets your requirements that you’ve been looking for. :slightly_smiling_face:

Thanks and Warm Regards,
Harsh

4

Here is the sample file.
Total_Days_bymnth.pbix (67.2 KB)

1 Like
5

Thanks for the file - very helpful. One question - are you tied to that particular date table or would it be OK if I replaced it with @Melissa 's awesome Extended Date Table? The latter will make this a snap…

  • Brian
6

Yes, the day counts are customized and tend to change a lot.

7

Thank you Harsh, unfortunately, I would need the days counts to be sourced from another table. Would that be possible with this measure?

8

@Harsh ,

Are you going to work this one? If so, that’s fine and I’ll step away and work other ones.

  • Brian
9

@supergallagher25 ,

Give this a go:

 Count Days = 

VAR IsLeaveDateBlank =
    ISBLANK( SELECTEDVALUE( tUsers[Leave Date] ) )
VAR vTable1 =
    FILTER( tDate, tDate[Work_Date] >= SELECTEDVALUE( tUsers[Enter Date] ) )
VAR vTable2 =
    FILTER(
        tDate,
        tDate[Work_Date] >= SELECTEDVALUE( tUsers[Enter Date] )
            && tDate[Work_Date] <= SELECTEDVALUE( tUsers[Leave Date] )
    )
VAR IfBlank =
    COUNTROWS( vTable1 )
VAR IfNotBlank =
    COUNTROWS( vTable2 )
VAR Result =
    IF( IsLeaveDateBlank = TRUE, IfBlank, IfNotBlank )
RETURN
    Result

image

A few things to watch out for:

  • you need to make sure to mark your date table as a date table
  • your date table should always include full years. As it is now your date table is truncated to a partial year which is why the August values look off. Best to keep your date table to a full year and filter via other means.

I hope this is helpful. Full solution attached below.

1 Like
10

Hello @supergallagher25 good to see that you are having progress with your inquiry.

Did the response from @BrianaJ help you solve your inquiry? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

11

Hi @supergallagher25, we’ve noticed that no response has been received from you since September 23.

We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

12

Hi @supergallagher25, due to inactivity, a response on this post has been tagged as “Solution”.

If you have a follow question or concern related to this topic, please create a new thread.

13

Thank you, Brian, this helped out a lot. Apologies for the late response, I had an emergency.