Hello, I'm struggling to find the answer to the below and would appreciate any advice anyone is able to offer.
I have loaded data into a cube as per the following:
LOAD [ASSET_NAME], [TYPE] RESIDENT All_Assets;
LEFT JOIN [WO_ID], [START_TIME], [END_TIME] RESIDENT Work_Orders;
The END_TIME has an autocalendar to get the year and month etc out do I can filter on it.
I have a chart where I've added Dimensions for [ASSET_NAME] and [TYPE], and then added measures for COUNT[WO_ID] as "No. Of Events", SUM([END_TIME] - [START_TIME]) as "Unavailable Hours" in order to calculate how many events the asset incurred and how long it was out of service during these events.
When I use the filters in a filter pane for year and month, the results filter out any work orders that fall outside these bounds which is what I want, but I want to get the earliest and latest dates available between the filtered values to then calculate the possible number of hours the asset COULD have been in service, and therefore allow me to calculate it's availability.
For example, if I filter on "YEAR = 2020" and "MONTH = March"
Then I'd like to calculate "31/03/2020 - 01/03/2020" as "Possible Hours" to get the number of hours in between them and add it as a column in the chart so I can calculate what percentage the "Unavailable Hours" is from this and return an availability figure.
Any help would be appreciated, thank you.
