Compare two date from different tables are not linked directly

[Hindlaz]

Hello,

I have two tables , Timesheet and Workflow that have same secondary key(TSkey) with other table TS Header.

I need to compare date from Timesheet if it submitted on time from table Workflow where the TSkey is same : TSkey contains timesheet number +Id resources and worflow.action=submitted.

Thank you in advance

[Anonymous]

Hi @Hindlaz ,

Thanks for the reply from RossEdwards .

Ensure that there are relationships established between your Timesheet table, Workflow table, and the TS Header table using the TSkey.

Create a measure:

Timesheet On Time = 
VAR TimesheetDate = MAX(Timesheet[Date])
VAR WorkflowDate = CALCULATE(MAX(Workflow[Date]), Workflow[Action] = "submitted", RELATED(TS_Header[TSkey]) = EARLIER(TS_Header[TSkey]))
RETURN IF(TimesheetDate <= WorkflowDate, "On Time", "Late")

Add it to your report visuals to see whether each timesheet was submitted on time or late.

If you have any other questions, please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[Hindlaz]

Hello,
Thank you for your replay,

The only realtionship that I have is that the table TS-header is intermediate btw Workflow and Timesheet  with the same key: TSKey

I tried your measure but it seems my PowerBI version is not accepting the function RELATED

[RossEdwards]

The solution i'd use is to store the TSKey in a variable in your measure and than apply it via a calculate statement.  When you use the measure on your report, it just needs to be in a visual in a manner that your TSKey always resolves down to a single value.  Here is an example I quickly wrote (untested):

YourMeasure = var TSKey = SELECTEDVALUE('Timesheet'[TSKey])
var TSDate = SELECTEDVALUE('Timesheet'[Timesheet Date])
var WFDate = CALCULATE(
    SELECTEDVALUE('Workflow'[Workflow Date]),
    'Workflow'[TSKey] = TSKey
)
var output = DATEDIFF(TSDate, WFDate , DAY)
RETURN
output