Average out of service days calculated column

[Alasharim]

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

Date	Truck No	Status	number of oos days	number of serviceable days
1-Jan	Truck 1	Serviceable		1
2-Jan	Truck 1	Serviceable		2
3-Jan	Truck 1	Serviceable		3
4-Jan	Truck 1	Serviceable		4
5-Jan	Truck 1	Serviceable		5
6-Jan	Truck 1	Serviceable		6
7-Jan	Truck 1	Serviceable		7
8-Jan	Truck 1	Serviceable		8
9-Jan	Truck 1	OOS	1
10-Jan	Truck 1	OOS	2
11-Jan	Truck 1	OOS	3
12-Jan	Truck 1	OOS	4
13-Jan	Truck 1	OOS	5
14-Jan	Truck 1	Serviceable		1
15-Jan	Truck 1	Serviceable		2
16-Jan	Truck 1	OOS	1
17-Jan	Truck 1	OOS	2
18-Jan	Truck 1	OOS	3
19-Jan	Truck 1	Serviceable		1
20-Jan	Truck 1	Serviceable		2
21-Jan	Truck 1	Serviceable		3
22-Jan	Truck 1	OOS	1
23-Jan	Truck 1	OOS	2
24-Jan	Truck 1	OOS	3
25-Jan	Truck 1	Serviceable		1
26-Jan	Truck 1	Serviceable		2
27-Jan	Truck 1	Serviceable		3
28-Jan	Truck 1	OOS	1
29-Jan	Truck 1	OOS	2
30-Jan	Truck 1	Serviceable		1
31-Jan	Truck 1	Serviceable		2

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

thank you in advance

[Anonymous]

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

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

[Alasharim]

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?

[Anonymous · 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

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

[Alasharim]

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