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

Hi Anonymous,

 

Thank you for reaching out to Microsoft Fabric Community Forum.

 

Create a calculated column using below DAX Expression.

Within5BusinessDays = 
VAR StartDate = 'Sheet1'[Exit Meeting]
VAR EndDate = 'Sheet1'[Final Audit Report]
VAR BusinessDays =
    CALCULATE(
        COUNTROWS(
            FILTER(
                ADDCOLUMNS(
                    CALENDAR(StartDate, EndDate),
                    "Weekday", WEEKDAY([Date], 2)
                ),
                [Weekday] <= 5
            )
        )
    )
RETURN IF(BusinessDays <= 5, "Yes", "No")

 

Result

 

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!


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