Hey everyone,
I am trying to figure the time that assets were on a car.
To make it simple have 2 sheets one with a car number and the sub asset number and the time frame that asset was on the car, for example Car 123 had asset ABC had an ontim of 1/1/2021 and an offtime of 3/1/2021.
The second sheet has the run time of the actual car number on it by each date.
So far I have wrote this.
=CALCULATE(SUM(Run_Time[RUN_TIME]), FILTER(Run_Time, Run_Time[CAR_NO] = Asset_Link[ASSET] && Run_Time[DATE_REV] >= Asset_Link[ONTIME] && Run_Time[DATE_REV] <= Asset_Link[OFFTIME]))
This works well and tells me the entire run time on the sub asset for it's entire life, but I want to group it by months.
Any idea here?
I am trying to figure the time that assets were on a car.
To make it simple have 2 sheets one with a car number and the sub asset number and the time frame that asset was on the car, for example Car 123 had asset ABC had an ontim of 1/1/2021 and an offtime of 3/1/2021.
The second sheet has the run time of the actual car number on it by each date.
So far I have wrote this.
=CALCULATE(SUM(Run_Time[RUN_TIME]), FILTER(Run_Time, Run_Time[CAR_NO] = Asset_Link[ASSET] && Run_Time[DATE_REV] >= Asset_Link[ONTIME] && Run_Time[DATE_REV] <= Asset_Link[OFFTIME]))
This works well and tells me the entire run time on the sub asset for it's entire life, but I want to group it by months.
Any idea here?