cancel
Showing results for
Did you mean:
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

 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?

1 ACCEPTED SOLUTION
Community Support

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

4 REPLIES 4
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``````

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

Frequent Visitor

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?

Community Support

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

Frequent Visitor

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)]
)``````

Announcements

#### The Power BI Community Show

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

#### Ted's Dev Camp - July 28, 2022

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