Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
PBITrust
Frequent Visitor

Days that a vehicle was non-operational

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:

OSIDStart DateEnd Date
OS 101/11/2023 03/11/2023
OS 202/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.

11 REPLIES 11
lbendlin
Super User
Super User

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.

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.

Dangar332
Super User
Super User

Hi, @PBITrust 

 

i don't know i get you or not but

 

look below image

Dangar332_0-1699275315340.png

 

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!

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)

 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.

Suporte.png
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.

Suporte 1.png


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

 

 

 

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

 

13,14,15,16 = four days.

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.

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.

lbendlin
Super User
Super User

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-...
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/1447...

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

Helpful resources

Announcements
August Power BI Update Carousel

Power BI Monthly Update - August 2025

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

August 2025 community update carousel

Fabric Community Update - August 2025

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