Complicated IF

Use Actual date if present, otherwise use Projected.

Then compute FundingDate - CommitDate (or DATEDIFF) only when both chosen dates exist.

2 ways to solve:

Solution 1: Power Query (M) - add a custom column

Open Power Query → Add Column → Custom Column and paste:

-----MCode-----

// name the new column "DaysToFunding" (or whatever you like)

let

CommitDate = if [Actual Commit Gate Date] <> null then [Actual Commit Gate Date] else [Projected Commit Gate Date],

FundingDate = if [Actual Full Funding Date] <> null then [Actual Full Funding Date] else [Projected Full Funding Date]

in

if CommitDate = null or FundingDate = null then null else Duration.Days(FundingDate - CommitDate)

-----MCode-----

• Duration.Days returns an integer number of days.
• If either chosen date is null the result is null (you can change to 0 or "-" if preferred).
• If you want absolute days (no negatives): replace last line with Number.Abs(Duration.Days(...)).

Solution 2: DAX - Calculated column

Create a calculated column in your table:

-----DAX-----

CommitChosen = COALESCE( 'Table'[Actual Commit Gate Date], 'Table'[Projected Commit Gate Date] )

FundingChosen = COALESCE( 'Table'[Actual Full Funding Date], 'Table'[Projected Full Funding Date] )

DaysToFunding =

VAR C = [CommitChosen]

VAR F = [FundingChosen]

RETURN

IF( NOT( ISBLANK(C) ) && NOT( ISBLANK(F) ),

DATEDIFF( C, F, DAY ),

BLANK()

)

-----DAX-----

• COALESCE picks the first non-blank (Actual preferred).
• DATEDIFF returns integer days (use DAY, MONTH, etc. as needed).
• If you want a measure (dynamic depending on filters) rather than a column, you can adapt the logic but the typical requirement is a calculated column for row-level date differences.

=================================================================

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Jaywant Thorat | MCT | Data Analytics Coach

Linkedin: https://www.linkedin.com/in/jaywantthorat/

Join #MissionPowerBIBharat = https://shorturl.at/5ViW9

#MissionPowerBIBharat

LIVE with Jaywant Thorat from 15 Dec 2025

8 Days | 8 Sessions | 1 hr daily | 100% Free