Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |