Forum Discussion

DM_BI's avatar
DM_BI
Icon for Helper III rankHelper III
7 years ago
Solved

Days between 2 dates in same table and by month name

Hello,

 

I have a table that has three columns, one is the person ID, one is the date they entered and the last column is the date they left. I am trying to create a new table on Power BI that calculates the occupancy for each client per month.

 

I have read a lot of topics and can't find something that works. 

 

I attach pbix and examples.

 

Thank you very much,

 

DM

 

https://www.dropbox.com/s/9gjwxikfwoipjbi/days%20between%202%20dates%20and%20by%20month.pbix?dl=0

https://www.dropbox.com/s/q0qij4lo09oo3bb/Occupancy1.png?dl=0

https://www.dropbox.com/s/p36deh0b894glvs/Occupancy2.png?dl=0

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi DM

     

    Does your calculation require "Time" to be taken into account or just days? If not, you can firstly create a calculated column, then create a measure. 

     

    The calculated column, will work out the days between the dates:

    OccupancyDays = 
DATEDIFF([entereddate], [leftdate], day)

    You can then create a sum measure to calculate the total time spent.

    Total Occcupancy = 
CALCULATE
      SUM([OccupancyDays])
)

    When you then drag in your person ID and the month into an axis, using your new measure you will see the total time spent.

     

    Hope that helps.

     

    Thanks