Using filter with DateDiff

[cindymei_chiu]

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!

[johnt75]

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

[v-vpabbu]

Hi @cindymei_chiu,

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

[cindymei_chiu]

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!!

[v-vpabbu]

Hi @cindymei_chiu,

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

[cindymei_chiu]

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!

[v-vpabbu · Accepted solution]

Hi @cindymei_chiu,

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

[cindymei_chiu]

That works!!  Thank you so much!!!

[cindymei_chiu]

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]

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

[cindymei_chiu]

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! 

[ajaybabuinturi]

Hi @cindymei_chiu, 

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