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

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Thank you so much!  Do I need to specific the column names or filter:

VAR StartDate = 'ProjectPhaseMilestones'[MilestonesName = "Exit Meeting"]
VAR EndDate = 'ProjectPhaseMilestones'[MilestonesName = "Final Audit Report"]
If without, both gave me the same error message that Exit Meeting and Final Audit Report cannot be found.  
 
I'm using both ActualEndDate for MilestoneName = "Exit Meeting" and "Final Audit Report":
 

Greatly appreciate your help!!

 
Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi Anonymous,

 

DAX doesn’t allow filtering like that directly in a column reference. Instead, you need to filter the table and extract the value from the ActualEndDate column where MilestonesName = "Exit Meeting".

 

Regards,

Vinay Pabbu

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi Vinay, 

    How do I do that?  Filter the table and extract the value from the ActualEndDate column where MilestonesName = "Exit Meeting" and "Final Audit Report".  

    Thank you!

    • Anonymous's avatar
      Anonymous
      Not applicable
      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

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        That works!!  Thank you so much!!!