Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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,
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,
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
User | Count |
---|---|
127 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |