topic Re: Days that a vehicle was non-operational in DAX Commands and Tips

Days that a vehicle was non-operational

PBITrust — Tue, 07 Nov 2023 21:50:18 GMT

Hi there,
(EDIT: This text have been update)

Objective: Return the exact quantity of days that a vehicle havent been operational.
My DAX:

NonOperationalDays = DATEDIFF ( MIN ( pq_fManutencao[Date_StartSO] ) , IF ( MAX ( pq_fManutencao[Date_EndSO] ) = BLANK() , TODAY() , MAX ( pq_fManutencao[Date_EndSO] ) ) , DAY )

My Problem: The current enterprise process allow the oppening of multiple Order of Services (O.S), this mean it is creating layers.

A single vehicle can have many OS happening at same time, and my DAX return the exact days per S.O, but not in a vehicle Context.
The figure bellow show:

Lets say that the Vehicle A in the Farm F have the following exemple:

OSID	Start Date	End Date
OS 1	01/11/2023	03/11/2023
OS 2	02/11/2023	05/11/2023

The vehicle was non operation from 01/11/2023 to 05/11/2023, by this 4 days.
In Dax it is unprecise, and if we use SUM by diference, it will return OS 1 as 2 days and OS 2 3 days, by this it is 5 days

Link to data sample: SuportePBI (2).xlsx
It will expire at 18/11/2023.

Re: Days that a vehicle was non-operational

lbendlin — Sat, 04 Nov 2023 14:05:24 GMT

Please provide sample data (with sensitive information removed) that covers your issue or question completely, in a usable format (not as a screenshot). Leave out anything not related to the issue.
If you are unsure how to do that please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.

If you want to get answers faster please refer to https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

Re: Days that a vehicle was non-operational

PBITrust — Mon, 06 Nov 2023 12:33:42 GMT

Hi lbendlin, I have provided now the sample data! As comented in the link that your refer! Thanks.

Re: Days that a vehicle was non-operational

Dangar332 — Mon, 06 Nov 2023 12:55:58 GMT

Hi, PBITrust

i don't know i get you or not but

look below image

Re: Days that a vehicle was non-operational

PBITrust — Mon, 06 Nov 2023 13:54:58 GMT

Hello Dangar332, It seems right! If i get you have SUM the collum of my sample right?
This (DAX) whas indeed a DAX an not a Calculated Colum, I'm curious if it work as a dax indeed.
But i will try here the Calculated Colum to see if it works fine with what i need.

Re: Days that a vehicle was non-operational

PBITrust — Mon, 06 Nov 2023 18:07:37 GMT

By Power Query, i have created a Calculated Column, but the result via SUM, is a acumulated value, to wich is not my intend.
By the means that this is indeed the days a vehicle was non operational, it isent a non cumulative view, beacuse as a vehicle can have multiple OS happening.

I cant say that the SUM(NonOperationalDays) / PotentialWork days are in the same "page", as the first can by its nature, accumulate alot more days than a week, month, year can haver.

( A better way to ilustrate is that we have layers of Service Orders (OS), and they can be above one another. And the sum of it bring us a acumulate value, and the desire output is would be a view in a flat way ).

So if i have the same Farm and Vehicle.
OS 1 Date 01/11/2023 - 02/11/2023 (1 day off)
OS 2 Date 01/11/2023 - 05/11/2023 ( 4 days off)

It havent take 5 days, it is 4 flat days.
Get it?
If Im not clear, tell me!

Re: Days that a vehicle was non-operational

lbendlin — Tue, 07 Nov 2023 00:05:16 GMT

This is impossible to do in DAX (because there is no UNIONX function - please vote for the idea) but very easy to do in Power Query.

let Source = Excel.Workbook(File.Contents("C:\Users\xxx\Downloads\SuportePBI.xlsx"), null, true), SuportePBI_Table = Source{[Item = "SuportePBI", Kind = "Table"]}[Data], #"Changed Type" = Table.TransformColumnTypes( SuportePBI_Table, { {"FarmId", type text}, {"VehicleId", Int64.Type}, {"O.S Id", type text}, {"Start Date", type date}, {"End Date", type date}, {"Days non Operational (DAX)", Int64.Type} } ), #"Grouped Rows" = Table.Group( #"Changed Type", {"VehicleId"}, { { "Rows", each _, type table [ FarmId = nullable text, VehicleId = nullable number, O.S Id = nullable text, Start Date = nullable date, End Date = nullable date, #"Days non Operational (DAX)" = nullable number ] } } ), #"Added Custom1" = Table.AddColumn( #"Grouped Rows", "Days non-operative", each List.Count( List.Distinct( List.Combine( Table.AddColumn( [Rows], "Custom", each { Int64.From([Start Date]) .. Int64.From([End Date] ?? Date.From(DateTime.LocalNow())) } )[Custom] ) ) ), Int64.Type ), #"Removed Other Columns" = Table.SelectColumns( #"Added Custom1", {"VehicleId", "Days non-operative"} ) in #"Removed Other Columns"

How to use this code: Create a new Blank Query. Click on "Advanced Editor". Replace the code in the window with the code provided here. Click "Done". (make sure to correct the path to the Excel file)

Re: Days that a vehicle was non-operational

PBITrust — Tue, 07 Nov 2023 13:23:17 GMT

Hey lbendlin thank you for the idea!
I used a amostral selection of 3 vehicles and the manual counting and the solution isen't working as desired.

Better explanation in bellow figure.
1. The -1 ID has 2 Service Orders, but in a flat view it just from 13 to 16, this means 3 not 4 days.

2. In this case, it has again 2 service orders, but both end the same day, so the result is 0, no day have pass, in this sample we I dont show the beging hour and ending one, but we endeed have it.
Sorry if i'm being a bad community member, but I havent seen it as needed, as there is no problem in it.

3. We here have 98 days, in four OS that contemplate the sum of the second and first problem.

If you need more information, or something else just comment me!
And again thank's for the help!

Re: Days that a vehicle was non-operational

lbendlin — Tue, 07 Nov 2023 14:07:03 GMT

just from 13 to 16, this means 3 not 4 days.

13,14,15,16 = four days.

Re: Days that a vehicle was non-operational

PBITrust — Tue, 07 Nov 2023 20:43:31 GMT

It indeed depend on the context, and in this case it is 3 days.

As the time it begun and the time it has end, may not be a cicle of full day, by this understanding and Service Order that has begun at 14hr (4pm) in day 13/10/2023 and ended at 12h (12am) of the 16th day, by this we have 3 days and 22 hours.

As said in my last comment here we have time hours that a Service Orders have begun and ended, but the calculation of the day is the moust trick part by now.

Re: Days that a vehicle was non-operational

lbendlin — Tue, 07 Nov 2023 20:55:23 GMT

There was no time component in your sample data. Please provide sample data that fully covers your issue.
Please show the expected outcome based on the sample data you provided.

Re: Days that a vehicle was non-operational

PBITrust — Tue, 07 Nov 2023 21:58:30 GMT

See if it better: SuportePBI (2).xlsx
I belive this will bring an adicional complication. As the hour in or ERP Addon, is been bringing in number, so a 06:00 pm (for my country 06pm is equal 18h and the system will bring 1800.
For now I'm working in finding a solution for it, wich shall be concluded if all goes alright by tomorrow morning (UTC-8).

So I will reupload a new version of this arquive.