Calcule the average duration on entry

I love these questions, they really make you think on how to approach the problem as a whole 🙂 Fun fun fun.

Anyway, this is my solution. First, I loaded your mockup data into powerbi (big thank you for providing that!). I added a calculated column which indicates if the current row was the last row in a streak for that person (ID) in that category. The calculated column DAX is as follows:

IsLastMonth? = 
VAR _curID = Table4[ID]
VAR _curCat = Table4[Category]
VAR _curExportDate = Table4[Export date]
VAR _hasNextMonth = COUNTROWS(FILTER(Table4, Table4[Export date] = EOMONTH(_curExportDate, 1) && Table4[ID] = _curID && Table4[Category] = _curCat))
RETURN
IF(_hasNextMonth = 1, FALSE, TRUE)

Results in:

Next stop was to add a calculated column that gives an index to the occurence of an ID in a specific category. Basically; I want to show 2 for every row that Mary returned later (april and may rows). This way, we can distinct these rows from earlier rows. The DAX is this:

IsLastMonth? = 
VAR _curID = Table4[ID]
VAR _curCat = Table4[Category]
VAR _curExportDate = Table4[Export date]
VAR _hasNextMonth = COUNTROWS(FILTER(Table4, Table4[Export date] = EOMONTH(_curExportDate, 1) && Table4[ID] = _curID && Table4[Category] = _curCat))
RETURN
IF(_hasNextMonth = 1, FALSE, TRUE)

(to be honest, this is the part I was most proud of, I had a big smirk on my face when I got it right haha), resulting in this:

Now that we have this 'index per category per ID' column, we can create a calculated table as a summary table of the above where we want the count of rows for every category, ID and IndexPerIdAndCategory, with the following DAX:

Table4AdjecentRowCount = SUMMARIZECOLUMNS(Table4[Category], Table4[ID], Table4[IndexPerIDandCategory], "Count", COUNTROWS(Table4))

Resulting in this table:

From here, you can create a table visual with column Category and the average of the Count column:

By the way, you stated the following for A: "(1+2+4)/3=3,5 Months on average" This is clearly incorrect; 7 / 3 = 2.33 (see my table above).

Anyway, here is the PBIX, this was a fun one and now it is time for bed.

https://1drv.ms/u/s!Ancq8HFZYL_aiItDUonqIklYNkn7Nw?e=F2dZF6 

Kind regards

Djerro123

-------------------------------

If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.

Keep those thumbs up coming! 🙂