Diference between 2 dates by period

[Anonymous]

Hi,

I need to calculate Time utilization like this:

TU = DIVIDE([Rented Out] , [Days])

I need the amount of rented out in days of each vehicle, but what I can not figure out is how to outcome by a selected period (diference of days betwen end date and start day):

Rented out is the amount of days with machines rented and Days, the amount of aviable days without weekends in the same period.

The problem is that my database has diferent machines whose are rented by day. I have a list of those machines with the start date (Fecha inicio) and end date (Fecha Fin). The report should be able to visualize TU by the user time selected.

I can not find out the way to calculate the workday (without weekend) and to calculate properly by month, for example if a machine starts june 29th  and ends july 5th should count 2 days in june, and 3 in july (without week end).

I don not know why NETWORKDAYS is not working.

This is my DAX formula and my database and calendar table.

Rented Out =

VAR Vehic =

CALCULATE(

COUNT(_Mateco_Union_MovimientoVehiculos[Vehiculo])

)

VAR Dates=

CALCULATE(

SUM(_Mateco_Union_MovimientoVehiculos[Fecha de fin corregida]) - SUM(_Mateco_Union_MovimientoVehiculos[Fecha Inicio]),

FILTER(Calendario , Calendario[Workday] = 1)

)

RETURN

CONVERT((Vehic + Dates), INTEGER)

Vehicle	Starting date	Ending date
XS3G75	31/01/2022 0:00	01/02/2022 0:00
XS3G75	01/02/2022 0:00	02/02/2022 0:00
NBT3 -5	11/01/2022 0:00	12/01/2022 0:00
N89M 372	11/02/2022 0:00	15/02/2022 0:00
XS3G75	02/02/2022 0:00	03/02/2022 0:00
NBT3 -5	02/02/2022 0:00	02/02/2022 0:00
NBT3 -5	12/01/2022 0:00	13/01/2022 0:00
XS3G75	11/01/2022 0:00	12/01/2022 0:00
NBT3 -5	02/02/2022 0:00	04/02/2022 0:00
XS3G75	12/01/2022 0:00	13/01/2022 0:00
XS3G75	13/01/2022 0:00	13/01/2022 0:00
XS3G75	03/02/2022 0:00	03/02/2022 0:00
XS3G75	03/02/2022 0:00	03/02/2022 0:00
XS3G75	03/02/2022 0:00	04/02/2022 0:00
XS3G75	13/01/2022 0:00	14/01/2022 0:00
NBT3 -5	13/01/2022 0:00	14/01/2022 0:00
N89M 372	15/02/2022 0:00	11/02/2022 0:00
N89M 372	11/02/2022 0:00	16/02/2022 0:00
N89M 372	16/02/2022 0:00	11/02/2022 0:00
N89M 372	11/02/2022 0:00	18/02/2022 0:00
XS3G75	04/02/2022 0:00	07/02/2022 0:00
NBT3 -5	04/02/2022 0:00	09/03/2022 0:00
NBT3 -5	14/01/2022 0:00	14/01/2022 0:00
XS3G75	14/01/2022 0:00	28/01/2022 0:00
N89M 372	07/02/2022 0:00	08/12/2021 0:00
XS3G75	07/02/2022 0:00	07/02/2022 0:00
XS3G75	07/02/2022 0:00	30/03/2022 0:00
NBT3 -5	14/01/2022 0:00	21/01/2022 0:00
NBT3 -5	21/01/2022 0:00	26/01/2022 0:00
N89M 372	08/02/2022 0:00	08/02/2022 0:00
N89M 372	18/02/2022 0:00	18/02/2022 0:00
N89M 372	08/02/2022 0:00	09/02/2022 0:00
N89M 372	18/02/2022 0:00	18/02/2022 0:00
N89M 372	18/02/2022 0:00	25/02/2022 0:00
N89M 372	09/02/2022 0:00	10/02/2022 0:00
N89M 372	10/02/2022 0:00	11/02/2022 0:00
N89M 372	07/03/2022 0:00	30/05/2022 0:00
N89M 372	25/02/2022 0:00	28/02/2022 0:00
NBT3 -5	26/01/2022 0:00	02/02/2022 0:00
NBT3 -5	09/03/2022 0:00	09/03/2022 0:00
N89M 372	28/02/2022 0:00	02/03/2022 0:00
NBT3 -5	09/03/2022 0:00	09/03/2022 0:00
NBT3 -5	09/03/2022 0:00	15/03/2022 0:00
N89M 372	02/03/2022 0:00	02/03/2022 0:00
N89M 372	02/03/2022 0:00	02/03/2022 0:00
N89M 372	02/03/2022 0:00	04/03/2022 0:00
NBT3 -5	15/03/2022 0:00	09/03/2022 0:00
XS3G75	28/01/2022 0:00	31/01/2022 0:00
N89M 372	04/03/2022 0:00	07/03/2022 0:00
NBT3 -5	09/03/2022 0:00	21/03/2022 0:00
XS3G75	01/04/2022 0:00	01/04/2022 0:00
XS3G75	01/04/2022 0:00	05/04/2022 0:00
NBT3 -5	01/04/2022 0:00	01/04/2022 0:00
NBT3 -5	01/04/2022 0:00	06/04/2022 0:00
XS3G75	05/04/2022 0:00	05/04/2022 0:00
XS3G75	05/04/2022 0:00	05/04/2022 0:00
XS3G75	05/04/2022 0:00	05/04/2022 0:00
XS3G75	05/04/2022 0:00	08/04/2022 0:00
NBT3 -5	06/04/2022 0:00	06/04/2022 0:00
NBT3 -5	06/04/2022 0:00	07/04/2022 0:00
NBT3 -5	07/04/2022 0:00	08/04/2022 0:00
XS3G75	05/05/2022 0:00	06/06/2022 0:00
NBT3 -5	08/04/2022 0:00	08/04/2022 0:00
NBT3 -5	08/04/2022 0:00	08/04/2022 0:00
XS3G75	08/04/2022 0:00	08/04/2022 0:00
XS3G75	08/04/2022 0:00	08/04/2022 0:00
NBT3 -5	08/04/2022 0:00	08/04/2022 0:00
NBT3 -5	08/04/2022 0:00	11/04/2022 0:00
NBT3 -5	11/04/2022 0:00	23/05/2022 0:00
XS3G75	08/04/2022 0:00	11/04/2022 0:00
XS3G75	11/04/2022 0:00	05/05/2022 0:00
NBT3 -5	23/05/2022 0:00	09/04/2022 0:00
NBT3 -5	21/03/2022 0:00	21/03/2022 0:00
NBT3 -5	09/04/2022 0:00	17/05/2022 0:00
NBT3 -5	17/05/2022 0:00	17/05/2022 0:00
NBT3 -5	17/05/2022 0:00	02/06/2022 0:00
N89M 372	02/06/2022 0:00	03/06/2022 0:00
NBT3 -5	02/06/2022 0:00	23/06/2022 0:00
N89M 372	03/06/2022 0:00	27/12/2046 0:00
N89M 372	30/05/2022 0:00	30/05/2022 0:00
XS3G75	13/06/2022 0:00	15/06/2022 0:00
N89M 372	30/05/2022 0:00	02/06/2022 0:00
NBT3 -5	21/03/2022 0:00	01/04/2022 0:00
XS3G75	06/06/2022 0:00	10/06/2022 0:00
XS3G75	15/06/2022 0:00	15/06/2022 0:00
XS3G75	15/06/2022 0:00	20/06/2022 0:00
NBT3 -5	23/06/2022 0:00	24/06/2022 0:00
NBT3 -5	24/06/2022 0:00	30/06/2022 0:00
NBT3 -5	30/06/2022 0:00	24/06/2022 0:00
XS3G75	20/06/2022 0:00	20/06/2022 0:00
XS3G75	20/06/2022 0:00	20/06/2022 0:00
XS3G75	20/06/2022 0:00	06/07/2022 0:00
XS3G75	30/03/2022 0:00	30/03/2022 0:00
NBT3 -5	24/06/2022 0:00	30/06/2022 0:00
NBT3 -5	30/06/2022 0:00	24/06/2022 0:00
NBT3 -5	24/06/2022 0:00	30/06/2022 0:00
NBT3 -5	30/06/2022 0:00	30/06/2022 0:00
XS3G75	30/03/2022 0:00	30/03/2022 0:00
NBT3 -5	30/06/2022 0:00	30/06/2022 0:00
XS3G75	30/03/2022 0:00	31/03/2022 0:00
NBT3 -5	30/06/2022 0:00	30/06/2022 0:00
NBT3 -5	30/06/2022 0:00	01/07/2022 0:00
NBT3 -5	01/07/2022 0:00	27/12/2046 0:00
XS3G75	31/03/2022 0:00	01/04/2022 0:00
XS3G75	06/07/2022 0:00	07/07/2022 0:00
XS3G75	07/07/2022 0:00	07/07/2022 0:00
XS3G75	07/07/2022 0:00	07/07/2022 0:00
XS3G75	12/07/2022 0:00	13/07/2022 0:00
XS3G75	07/07/2022 0:00	08/07/2022 0:00
XS3G75	13/07/2022 0:00	27/12/2046 0:00
XS3G75	08/07/2022 0:00	12/07/2022 0:00
XS3G75	10/06/2022 0:00	13/06/2022 0:00

[v-chenwuz-msft · Accepted solution]

Hi @Anonymous ,

NETWORKDAYS() will only return the number of workdays between start date and end date than each month.

To get the result you want, please try this.

1 add one more column in date table via Month = [date].[month]

2 create a new calcualte table via 

Table_month = filter(CROSSJOIN('Table','Date'),[Date]>=[Starting Date] && [Date]<=[Ending date])

to expend each date between starting date and ending date.

3 create a measure to count how mang days are there during starting and ending date.
countrows('Table_month')

Result:

Pbix in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.