Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Using filter with DateDiff

Hi All,  I'm new to pbi and need help with the following table to calculate the right data: I want to calculate the datediff using actual end date from exit meeting to the actual end date on ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Anonymous,

     

    Try this measure using CALCULATE Function 

    VAR ExitDate =
    CALCULATE(
        MAX('ProjectPhaseMilestones'[ActualEndDate]),
        'ProjectPhaseMilestones'[MilestonesName] = "Exit Meeting"
    )

VAR FinalAuditDate =
    CALCULATE(
        MAX('ProjectPhaseMilestones'[ActualEndDate]),
        'ProjectPhaseMilestones'[MilestonesName] = "Final Audit Report"
    )

     

    Regards,

    Vinay Pabbu

johnt75's avatar
johnt75
Super User
1 year ago

Presuming that you have an ID column to match the two rows together you could create a measure like

Within 5 business days =
VAR CurrentID =
    SELECTEDVALUE ( 'Table'[ID] )
VAR ExitMeeting =
    LOOKUPVALUE (
        'Table'[Actual End Date],
        'Table'[ID], CurrentID,
        'Table'[Milestone Name], "Exit Meeting"
    )
VAR FinalAudit =
    LOOKUPVALUE (
        'Table'[Actual End Date],
        'Table'[ID], CurrentID,
        'Table'[Milestone Name], "Final Audit Report"
    )
VAR Result =
    IF ( NETWORKDAYS ( ExitMeeting, FinalAudit ) <= 5, "Yes", "No" )
RETURN
    Resul