Dax Help with Percentage shifts covered by Unit

[yve214]

Hi there,

Please I need help with some dax. I have been stuck on a problem and I need some guidance to obtain the outputs below.

1. I need to be able to calculate the percentage of shifts covered within in a 24 hour period between a selected range of dates say like (11/1/2021 - 12/30/2021) . For instance, looking at this small sample data betwen 11/1/2021 - 11/2/2021,  I need to be able to calculate shifts covered within a 24hour period as a percentage and be able to view the average results grouped by location and or Unit.

Please let me know if I need to clarify anything.

Thank you verymuch.

Unit	Location	Date	Shift starts	Shift ends	Hours
a	living room	11/1/2021	10/31/2021 9:00 PM	11/1/2021 5:00 AM	8
a	living room	11/1/2021	11/1/2021 5:00 AM	11/1/2021 1:00 PM	8
a	living room	11/1/2021	11/1/2021 1:00 PM	11/1/2021 9:00 PM	8
a	kitchen	11/1/2021	11/1/2021 12:00 AM	11/1/2021 8:00 AM	8
a	kitchen	11/1/2021	11/1/2021 12:00 PM	11/1/2021 8:00 PM	8
b	Room1	11/1/2021	11/1/2021 12:00 AM	11/1/2021 12:00 PM	12
b	Room1	11/1/2021	11/1/2021 12:00 PM	11/1/2021 10:00 PM	10
c	Dining	11/1/2021	11/1/2021 6:00 AM	11/1/2021 2:00 AM	8
c	Closet	11/1/2021	10/31/2021 9:00 PM	11/1/2021 5:00 AM	8
c	Closet	11/1/2021	11/1/2021 5:00 AM	11/1/2021 1:00 PM	8
c	Closet	11/1/2021	11/1/2021 1:00 PM	11/1/2021 9:00 PM	8
a	living room	11/2/2021	10/31/2021 9:00 PM	11/1/2021 5:00 AM	8
a	living room	11/2/2021	11/1/2021 5:00 AM	11/1/2021 1:00 PM	8

Expected Output 1 assumming a 24hour period i.e. each day:

Unit	Location	Date	Total Hours	% Coverage
a	living room	11/1/2021	24	100%
a	living room	11/2/2021	16	67%
b	Room1	11/1/2021	22	92%
c	Dining	11/1/2021	8	33%
c	Closet	11/1/2021	24	100%

Output 2:

Unit	% Coverage
a	83%
b	92%
c	67%

[VahidDM · Accepted solution]

Hi @yve214 

Can you add more details about how did you calculate the  % Coverage?

BTW, try this measure:

% Coverage =

Var _C = DISTINCTCOUNT('Table'[Date])

return

sum('Table'[Hours])/(_C*24)

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[V-lianl-msft]

Maybe I understand what you mean.Please try to modify the formula provided before like below:

% Coverage = 
Var _C = DISTINCTCOUNT('Table'[Date])+DISTINCTCOUNT('Table'[Location])-1
return
sum('Table'[Hours])/(_C*24)
 

[VahidDM · Accepted solution]

Hi @yve214 

Can you add more details about how did you calculate the  % Coverage?

BTW, try this measure:

% Coverage =

Var _C = DISTINCTCOUNT('Table'[Date])

return

sum('Table'[Hours])/(_C*24)

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[yve214 · Accepted solution]

Hi Vahid,

Thank you very much for your help, This helped and i got exactly what i needed.