Accessing Previous Rows in Filtered Context

[Anonymous]

The data I'm trying utilize exists in the following context:

Different equipment is on site for various quantities for varying amounts of time, as determined by dates on site.

I need to establish the calculated days on site as follows:

For the Power Console:

  Qty On Site:   10          8          5

  Days On Site:  3        3+2        3+2+1

I established the qty index to rank the quanities thinking I could then use this to grab the previous days value (2) for the lower ranked 8 qty and add it to the value of  3 days to get a total of five days on site.  Unfortunately the previous index grabbed appears to be one that exists in the raw data outside the filter context and is associated with another random row in the raw data.

Is there a way to get a previous value for the days column that takes into account the filtered context of the matrix?

A subset of the data:

formDataGuid	rowGuid	equipment_description	equipment_qty	Equipment Days	Qty Index	Date_
0f17632e-aa44-4c7b-9f0a-0452fb094251	d6e0bdc9-c3cb-4337-979b-0f0f4433f7d9	Truck	8	3	8	08-Aug-21
0f17632e-aa44-4c7b-9f0a-0452fb094251	84bf9557-3049-4faf-bdeb-ef1f4c24bcb6	Chainfall	2	5	4	08-Aug-21
0f17632e-aa44-4c7b-9f0a-0452fb094251	5590c92a-93a3-4d77-9845-9570ee6ca0f8	Power Console	10	3	9	08-Aug-21
20d8daf7-b9f1-42e4-b888-8f3d2a48540b	bf8ab457-5b92-4c7b-9db2-c3834bc6746e	Truck	8	3	8	11-Aug-21
20d8daf7-b9f1-42e4-b888-8f3d2a48540b	876388ee-9f3d-4e23-80e2-6d77b3429537	Chainfall	2	5	4	11-Aug-21
20d8daf7-b9f1-42e4-b888-8f3d2a48540b	1bf5d3f7-6211-4b8a-a900-ae64910665c7	Power Console	10	3	9	11-Aug-21
32f0fc9f-214a-45cb-b739-9ca61c7cde61	2fc80227-ad2b-4bf1-a243-2cab25e0786d	Truck	2	1	4	07-Aug-21
32f0fc9f-214a-45cb-b739-9ca61c7cde61	205644b2-6ed6-40b0-bb5b-a79cf41d866a	Chainfall	4	1	5	07-Aug-21
32f0fc9f-214a-45cb-b739-9ca61c7cde61	0ace0683-e690-4772-b3cc-4bdddc2f9bcb	Power Console	5	1	6	07-Aug-21
3aea03f7-9afb-46e3-b3d9-c0654aa32fb4	f5118d85-8abf-41e4-b04f-5306a4d6424b	Truck	5	2	6	12-Aug-21
3aea03f7-9afb-46e3-b3d9-c0654aa32fb4	e7fb97cb-db76-4c14-bdf2-8d8307226da0	Power Console	8	2	8	12-Aug-21
3aea03f7-9afb-46e3-b3d9-c0654aa32fb4	9be3ba09-1c82-404a-ac2b-baa0a3f4f51f	Chainfall	2	5	4	12-Aug-21
98bc23fb-a78d-4bbc-8c70-2b0cedabb8bb	9aa994f5-e87b-4793-8ba6-8aaf5f0e3696	Power Console	10	3	9	10-Aug-21
98bc23fb-a78d-4bbc-8c70-2b0cedabb8bb	69294bdb-64ac-4cbe-97cf-27039c8fe9ea	Chainfall	2	5	4	10-Aug-21
98bc23fb-a78d-4bbc-8c70-2b0cedabb8bb	68d0ec31-e361-40b9-bb9a-2327ac141069	Truck	8	3	8	10-Aug-21

The qty index column is calculated as 

Qty Index = rankx(form_itemized_field_ticket_equipment_row, form_itemized_field_ticket_equipment_row[equipment_qty],,1,Dense)

The Equipment Days Test is a Measure:

Equipment Days Test = VAR _CurrentRowIndex = max(form_itemized_field_ticket_equipment_row[Qty Index]) VAR _Equipment = max(form_itemized_field_ticket_equipment_row[Equipment No and Description]) VAR _Job = FIRSTNONBLANK(form_itemized_field_ticket[job_no], form_itemized_field_ticket[job_no]) VAR _qty = max(form_itemized_field_ticket_equipment_row[equipment_qty]) VAR _PreviousRowIndex = Calculate(MAX(form_itemized_field_ticket_equipment_row[Qty Index]), FILTER(form_itemized_field_ticket_equipment_row, form_itemized_field_ticket_equipment_row[Equipment No and Description] = _Equipment && related(form_itemized_field_ticket[job_no]) = _Job))  VAR _Result = Calculate(DISTINCTCOUNT(form_itemized_field_ticket[date_]), filter(form_itemized_field_ticket_equipment_row, form_itemized_field_ticket_equipment_row[equipment_qty] <> BLANK() && form_itemized_field_ticket_equipment_row[Equipment No and Description] = _Equipment && related(form_itemized_field_ticket[job_no]) = _Job && form_itemized_field_ticket_equipment_row[equipment_qty] = _qty)) RETURN _Result

I've attempted to add a _previousresult VAR to the above from which to calculate _Result + _previousresult to return without any success in writing a formulation that works.

One of my many attempts below to put the Measure that is Equipment Days Test into a column format that would make the calculation easier:

Instead of days on site of  1, 2, 3  this gives 1, 4, 9 however.

Equipment Days = VAR _CurrentRowIndex = form_itemized_field_ticket_equipment_row[Qty Index] VAR _Equipment = form_itemized_field_ticket_equipment_row[Equipment No and Description] VAR _Job = related(form_itemized_field_ticket[job_no]) VAR _qty = form_itemized_field_ticket_equipment_row[equipment_qty] VAR _PreviousRowIndex = Calculate(MAX(form_itemized_field_ticket_equipment_row[Qty Index]), FILTER(form_itemized_field_ticket_equipment_row, form_itemized_field_ticket_equipment_row[Equipment No and Description] = _Equipment && related(form_itemized_field_ticket[job_no]) = _Job && form_itemized_field_ticket_equipment_row[Qty Index] < _CurrentRowIndex )) VAR _PreviousResult = Calculate( MIN(form_itemized_field_ticket_equipment_row[equipment_qty]), Filter(form_itemized_field_ticket_equipment_row, form_itemized_field_ticket_equipment_row[Qty Index] = _PreviousRowIndex)) VAR _Result = Calculate(DISTINCTCOUNT(form_itemized_field_ticket[date_]), filter(form_itemized_field_ticket_equipment_row, form_itemized_field_ticket_equipment_row[equipment_qty] <> BLANK() && form_itemized_field_ticket_equipment_row[Equipment No and Description] = _Equipment && related(form_itemized_field_ticket[job_no]) = _Job && form_itemized_field_ticket_equipment_row[equipment_qty] = _qty)) RETURN _Result

Any pointers or tips would be appricaited at this point.  I've attempted to create secondary tables, convert to columns, utilize measures and I'm stuck.

[Greg_Deckler]

@Anonymous Should be posssible. See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/3395....
The basic pattern is:
Measure = 
  VAR __Current = MAX([Value])
  VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])

  VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
  __Current - __Previous

You could use a measure like this in an ADDCOLUMNS statement within another measure to achieve what you want.