Reset cummulative total/running total when other column value is TRUE

justlogmein — Sun, 12 Jun 2022 06:26:07 GMT

What I want to do is pretty straightforward but all the examples I have tried have not worked so far and I'm stressing a little because I need to complete this today.

I have a table of engine oil samples for heavy mining machinery and a column called "Engine Oil Changed" which indicates if the engine oil was changed when that sample was taken. Now I simply want to find out how many hours the oil had before it was changed. I have a running total column for the hours which works fine. I just need to reset this running total when the "Engine Oil Changed" column is true, noting that I need to have it grouped by "Unit Number".

Is anyone able to provide some code to put into this column?

https://jmservicescomau-my.sharepoint.com/:u:/g/personal/jacob_jmservices_com_au/EaFFuIzsncpKiGoUSuwxrWUBVk8_IllZCqvVeT0G4jeqPg?e=8xJmDN

Re: Reset cummulative total/running total when other column value is TRUE

tamerj1 — Sun, 12 Jun 2022 14:24:05 GMT

justlogmein
Hope it's not too late 🙂

Here is the sample file with the solution https://www.dropbox.com/t/HZEL59KsSFlicaPV
Please use the following code

Fluid Hours Reset when Oil Changed = IF ( 'Sample Data'[Oil Changed] = FALSE ( ), VAR CurrentDate = 'Sample Data'[Sample Date] VAR DatesBeforeTable1 = FILTER ( 'Sample Data', 'Sample Data'[Sample Date] <= CurrentDate ) VAR DatesBeforeTable2 = FILTER ( 'Sample Data', 'Sample Data'[Sample Date] < CurrentDate ) VAR PrevuiousChangesTable = FILTER ( DatesBeforeTable2, 'Sample Data'[Oil Changed] = TRUE ( ) ) VAR PreviousChangeDate = MAXX ( PrevuiousChangesTable, 'Sample Data'[Sample Date] ) VAR ResultTable = FILTER ( DatesBeforeTable1, 'Sample Data'[Sample Date] > PreviousChangeDate ) RETURN SUMX ( ResultTable, 'Sample Data'[Hours Since Previous Reading] ) )

If you want to include the last change hours with the sum use this code with a very tiny change

Fluid Hours Reset when Oil Changed = IF ( 'Sample Data'[Oil Changed] = FALSE ( ), VAR CurrentDate = 'Sample Data'[Sample Date] VAR DatesBeforeTable1 = FILTER ( 'Sample Data', 'Sample Data'[Sample Date] <= CurrentDate ) VAR DatesBeforeTable2 = FILTER ( 'Sample Data', 'Sample Data'[Sample Date] < CurrentDate ) VAR PrevuiousChangesTable = FILTER ( DatesBeforeTable2, 'Sample Data'[Oil Changed] = TRUE ( ) ) VAR PreviousChangeDate = MAXX ( PrevuiousChangesTable, 'Sample Data'[Sample Date] ) VAR ResultTable = FILTER ( DatesBeforeTable1, 'Sample Data'[Sample Date] >= PreviousChangeDate ) RETURN SUMX ( ResultTable, 'Sample Data'[Hours Since Previous Reading] ) )

topic Re: Reset cummulative total/running total when other column value is TRUE in DAX Commands and Tips

Reset cummulative total/running total when other column value is TRUE

Re: Reset cummulative total/running total when other column value is TRUE