Forum Discussion

Helper V

10 years ago

Combine Date Table?

I have a Date Table that I populated and created a relationship to a table of scheduled requests (SRs) via their start date (not end date). every SR has an assigned user. Not every assigned user ...

Microsoft Employee

10 years ago

Hi,

I assume you have a table “requestInfo” table like below.

Firstly, create a calculated column to get the last end date:

Column =
VAR PreID = requestInfo[requestId] - 1
RETURN
(
IF( LOOKUPVALUE( requestInfo[assigned user], requestInfo[requestId], PreID ) <> requestInfo[assigned user], requestInfo[end date],
LOOKUPVALUE( requestInfo[end date], requestInfo[requestId], PreID )
))

Secondly, create another column to calculated free days between SRs:

freeDays = if([start date] < [Column], 0, DATEDIFF([Column], [start date], DAY)-1)

Now, you can create a table to get all free days for users:

Table = CALCULATETABLE(SUMMARIZECOLUMNS(requestInfo[assigned user], "free days", sum(requestInfo[freeDays])))

Best Regards

Alex