cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Alasharim
Frequent Visitor

Average out of service days calculated column

hi all,

 

I'm trying to calculate the average out-of-service days for a certain Truck...

 

below is an example of the table I'm dealing with

 

the first three columns are what I have and the last two columns are what I'm trying to calculate

 

DateTruck NoStatusnumber of oos daysnumber of serviceable days
1-JanTruck 1Serviceable 1
2-JanTruck 1Serviceable 2
3-JanTruck 1Serviceable 3
4-JanTruck 1Serviceable 4
5-JanTruck 1Serviceable 5
6-JanTruck 1Serviceable 6
7-JanTruck 1Serviceable 7
8-JanTruck 1Serviceable 8
9-JanTruck 1OOS1 
10-JanTruck 1OOS2 
11-JanTruck 1OOS3 
12-JanTruck 1OOS4 
13-JanTruck 1OOS5 
14-JanTruck 1Serviceable 1
15-JanTruck 1Serviceable 2
16-JanTruck 1OOS1 
17-JanTruck 1OOS2 
18-JanTruck 1OOS3 
19-JanTruck 1Serviceable 1
20-JanTruck 1Serviceable 2
21-JanTruck 1Serviceable 3
22-JanTruck 1OOS1 
23-JanTruck 1OOS2 
24-JanTruck 1OOS3 
25-JanTruck 1Serviceable 1
26-JanTruck 1Serviceable 2
27-JanTruck 1Serviceable 3
28-JanTruck 1OOS1 
29-JanTruck 1OOS2 
30-JanTruck 1Serviceable 1
31-JanTruck 1Serviceable 2

 

 

is it advisable to take the calc column route or a measure is better performance-wise?

 

thank you in advance

1 ACCEPTED SOLUTION

Hi @Alasharim ,

 

You may consider converting calculated columns to measures.

Calculated Columns and Measures in DAX - SQLBI

 

number of oos days_1 = 
VAR _date =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Status] <> "OOS"
                && 'Table'[Date] <= max('Table'[Date])
                && 'Table'[Truck No] = MAX('Table'[Truck No])
        )
    )
VAR _times =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] <= MAX('Table'[Date])
                && 'Table'[Date] > _date
                && 'Table'[Truck No] =MAX('Table'[Truck No] )
        )
    )
RETURN
    _times
number of serviceable days_1 = 
VAR _date =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Status] <> "Serviceable"
                && 'Table'[Date] <= MAX ( 'Table'[Date] )
                && 'Table'[Truck No] = MAX ( 'Table'[Truck No] )
        )
    )
VAR _times =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] <= MAX( 'Table'[Date] )
                && 'Table'[Date] > _date
                && 'Table'[Truck No] = MAX( 'Table'[Truck No] )
        )
    )
RETURN
    _times

vcgaomsft_0-1650520894700.png

Attach the PBIX file for reference. Hope it helps.

 

Best Regards,
Community Support Team_Gao

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems with it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

View solution in original post

4 REPLIES 4
v-cgao-msft
Community Support
Community Support

Hi @Alasharim ,

 

I use the calculated column.

number of oos days = 
VAR _date =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Status] <> "OOS"
                && 'Table'[Date] <= EARLIER ( 'Table'[Date] )
                && 'Table'[Truck No] = EARLIER ( 'Table'[Truck No] )
        )
    )
VAR _times =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] <= EARLIER ( 'Table'[Date] )
                && 'Table'[Date] > _date
                && 'Table'[Truck No] = EARLIER ( 'Table'[Truck No] )
        )
    )
RETURN
    _times
number of serviceable days = 
VAR _date =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Status] <> "Serviceable"
                && 'Table'[Date] <= EARLIER ( 'Table'[Date] )
                && 'Table'[Truck No] = EARLIER ( 'Table'[Truck No] )
        )
    )
VAR _times =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] <= EARLIER ( 'Table'[Date] )
                && 'Table'[Date] > _date
                && 'Table'[Truck No] = EARLIER ( 'Table'[Truck No] )
        )
    )
RETURN
    _times

vcgaomsft_0-1649759605594.png

Attach the PBIX file for reference. Hope it helps.

 

Best Regards,
Community Support Team_Gao

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems with it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

i went through your logic and it seems promising thank you.

 

the only issue is that it's taking too long for it to process through my large table.

 

i think a measure would be a more efficient approuch, what do you think?

Hi @Alasharim ,

 

You may consider converting calculated columns to measures.

Calculated Columns and Measures in DAX - SQLBI

 

number of oos days_1 = 
VAR _date =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Status] <> "OOS"
                && 'Table'[Date] <= max('Table'[Date])
                && 'Table'[Truck No] = MAX('Table'[Truck No])
        )
    )
VAR _times =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] <= MAX('Table'[Date])
                && 'Table'[Date] > _date
                && 'Table'[Truck No] =MAX('Table'[Truck No] )
        )
    )
RETURN
    _times
number of serviceable days_1 = 
VAR _date =
    CALCULATE (
        MAX ( 'Table'[Date] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Status] <> "Serviceable"
                && 'Table'[Date] <= MAX ( 'Table'[Date] )
                && 'Table'[Truck No] = MAX ( 'Table'[Truck No] )
        )
    )
VAR _times =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Date] <= MAX( 'Table'[Date] )
                && 'Table'[Date] > _date
                && 'Table'[Truck No] = MAX( 'Table'[Truck No] )
        )
    )
RETURN
    _times

vcgaomsft_0-1650520894700.png

Attach the PBIX file for reference. Hope it helps.

 

Best Regards,
Community Support Team_Gao

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems with it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

the measure version is much easier to deal with it takes a while for it to load though..

 

especially when coupling it with 

 

Avg OOS Days_M =
AVERAGEX (
    FILTER (
        'Table',
        'Table'[Date] = MAX ( 'Table'[Date] )
            && 'Table'[Status] = "OOS"
    ),
    [# of OOS Days (m)]
)

 

appreciate your thoughts here

 

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors