Calculating Max waiting time on any given date by group

[Anonymous]

Hi,

I'd like to ask your help with the following issue:

I have a table, teams, starting and finishing jobs. When the finish field is empty it means it's still ongoing. I'd need a table/graph, which shows me on any day what was the maximum waiting time. Let's say it's 04/Feb, row02 doesn't play anymore, only rows 03, 04 and 05. And then on 04/Feb the max waiting time was 2 days. I tried the below calculated table, but that doesn't return quite right, because altough it returns the correct row, but the corresponding result gives maximum waiting time always for today and not for any past days.

(The data sample and expected result is not the same as the sample calculated table and graph.)

Assistance is much appriciated. 

I have a table DataExpected outcomebecause this doesn't look rightthis seemed a good idea, but failed

[v-chuncz-msft]

@Anonymous ,

You may try using MAXX, FILTER and DATEDIFF.

[Anonymous]

Hi,

I tried to add the DATEDIFF, but it's proved too rigid to a calculated table. The condition is that on a given day the start date is either the same or eariler and the finish date is either empty or later, but there is no set of fix dates apart from this conditions. I was thinking on the possibility that there is no solution for this question, but the funny thing is the that the above calcuated table work brilliantly with aggregated functions, ie. count of row, or total waiting time, even average waiting time. The issues came when I search on a single value, such as MAX, but on any row. The below table is still functioning, just not giving me the answer I need, it returns the MAX as of TODAY, but not for historical states. Let's say on the example the parent row of the result gives 400 days today, but then it should be 399 for yesterday, and so on, and so on. Consequently the at some point of time the parent of the MAX likely to have been a different row as the 400 gradually goes down to zero and there are other rows in the dataset, but as the graph shows that's part is functioning. I think the problem may be, that actually could be more than one result fit to cirterias.

Thanks,

Balazs