Days between 2 dates in same table and by month name

[DM_BI]

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

[v-cherch-msft · Accepted solution]

Hi @DM_BI 

You may create a measure like below:

Measure = 
CALCULATE (
    COUNTROWS ( DimDate ),
    FILTER (
        GENERATE ( DimDate, Hoja1 ),
        Hoja1[Admit] < DimDate[Date]
            && Hoja1[Departure] >= DimDate[Date]
    )
)

Regards,

[Anonymous]

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