cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Diference between 2 dates by period

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
1 ACCEPTED SOLUTION
Community Support

Hi @Anonymous ,

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

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.

Community Support

Hi @Anonymous ,

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

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.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors