Multiply no of working days

[Fathopes]

Hi All, Lets say I have datasets as below for Lorry A and Lorry B. Each lorry have their own specific capacity. Based on the formula for utilization rate, how can I mutiply the no of days for the lorry. Example we can see for lorry B, we have two working days. This should be work when we filter by date based on days, week, month. Appreciate your help on this.

Lorry	Collection Date	Volume (kg)	Capacity (kg/day)	Utilization Rate (Volume/(Capacity * No of Day))	Utilization Rate % (Utilization Rate * 100)
A	1/1/2022	30	200	0.150	15.00
A	1/1/2022	40	200	0.200	20.00
B	2/1/2022	60	300	0.100	10.00
B	3/1/2022	70	300	0.117	11.67
B	3/1/2022	40	300	0.067	6.67

Thanks and Regards,

Fathopes.

[Anonymous · Accepted solution]

Hi @Fathopes ,

Please check the formula.

_rate =

var _days = CALCULATE(DISTINCTCOUNT('Table'[Collection Date]),FILTER('Table','Table'[Lorry]=EARLIER('Table'[Lorry])))

return

'Table'[Volume (kg)]/('Table'[Capacity (kg/day)]*_days)

Best Regards,

Jay

[Anonymous]

Hi @Fathopes 
Firstly I made a summary table like this 

Lorry_num_days = 
SUMMARIZECOLUMNS('Lorry Data'[Lorry],"Num_days",DISTINCTCOUNT('Lorry Data'[Collection Date]))

Then I made a calculated column in the original table like this

Util Rate Column =
DIVIDE(
'Lorry Data'[Volume (kg)],
'Lorry Data'[Capacity (kg/day)] * LOOKUPVALUE(Lorry_num_days[Num_days],Lorry_num_days[Lorry],'Lorry Data'[Lorry])
)

Now you can multiply this column by 100 to get the utilization rate %.

Regards,
Aditya

[amitchandak]

@Fathopes , The information you have provided is not making the problem clear to me. Can you please explain with an example.

refer if this can help

Traveling Across Workdays - What is next/previous Working day
https://community.powerbi.com/t5/Community-Blog/Travelling-Across-Workdays-Decoding-Date-and-Calenda...

Check for comments for new column

Appreciate your Kudos.

[Fathopes]

Hi, I add no of days row based on the collection date to make it clear. Basically I would like to calculate the utilization rate( in red font) in power bi. Please refer the formula below for utilization rate. So how can I get the no of days based on collection date and from that I can calculate the utilization rate. Hope this info is helpful. 

Lorry	Collection Date	Volume (kg)	Capacity (kg/day)	No of day	Utilization Rate (Volume/(Capacity * No of Day))	Utilization Rate % (Utilization Rate * 100)
A	1/1/2022	30	200	1	0.150	15.00
A	1/1/2022	40	200	1	0.200	20.00
B	2/1/2022	60	300	2	0.100	10.00
B	3/1/2022	70	300	2	0.117	11.67
B	3/1/2022	40	300	2	0.067	6.67