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):

Outcome

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 Inicio = CALCULATE( MIN(Starting date) )

VAR Fin =  CALCULATE( MAX([Ending date]))

VAR Laborales = NETWORKDAYS(Inicio, Fin)

VAR Diferencia =CALCULATE( Laborales ,  'Calendar'[Date] <= Inicio && 'Calendario' [Date] >= Fin, DATESBETWEEN('Calendario'[Date] , FIRSTDATE('Calendario inicio'[Date]), ENDOFMONTH('Calendario inicio'[Date])))

return

Diferencia

This is mi data:

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-jianboli-msft · Accepted solution]

Hi @Anonymous ,

I made some changes based on your original DAX:

First create a weekday column in calendar table to help calculate:

weekday = WEEKDAY([Date],2)

Then apply it to the changed DAX :

Rented out =

VAR Inicio = CALCULATE( MIN('Table'[Starting date]) )



VAR Fin =  CALCULATE( MAX('Table'[Ending date]))



VAR Laborales = CALCULATE(COUNT('Calendar'[Date]),FILTER('Calendar',[weekday]<=5&&[Date]<=Fin&&[Date]>=Inicio))



VAR Diferencia =CALCULATE( Laborales ,  'Calendar'[Date] <= Inicio && 'Calendar'[Date] >= Fin, DATESBETWEEN('Calendar'[Date] , FIRSTDATE('Calendar'[Date]), ENDOFMONTH('Calendar'[Date])))



return

Diferencia

Output:

If the result is not what you want, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

Refer to:

How to provide sample data in the Power BI Forum

How to Get Your Question Answered Quickly

Best Regards,

Jianbo Li

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