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 final audit report, and calculate if the date difference is within 5 business days, like the table below in excel:

 

I only managed to get this matrix in pbi and got stuck using DAX:

Any help would be much appreciated!!

Thank you!

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

11 Replies

  • 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
  • ajaybabuinturi's avatar
    ajaybabuinturi
    Super User
    1 year ago

    Hi Anonymous, 

    Could you please share sample data of .pbix file and expected result mockup

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    The pbix table is included in the first screen view and my expected result is the excel in the second screen view.  Thank you!

    • ajaybabuinturi's avatar
      ajaybabuinturi
      Super User
      1 year ago

      The question makes some confuesion that's why I am asking .pbix file

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Sorry for any confusion. My table has a milestonenames which includes annoucemene letter, exit meeting, final audit report and finalization.    The actual end date is in another column.  I want to pull in and compare the actual end date for exit meeting and final audit report, and to calculate if these 2 dates are within 5 business days.  I can easily do this in excel but since my other tables are in pbix, i want to see if i can create the same in it.  Thanks for your help! 

  • 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