Complicated IF

[rosamhernandez1]

edited to correct table and add more detail

I have 4 dates that I need to compare to each other, a Projected or Actual Commit Date and a Projected or Actual Funding Date... I have only been able to adjust the formula to incorporate 3 of these dates. I have:

if [Projected Commit Gate Date] <> null then
[Projected Full Funding Date]-[Projected Commit Gate Date] else
[Projected Full Funding Date]-[Actual Commit Gate Date]

now we have more and more projects that have Actual instead of Projected Dates, like so:

Projected Commit	Actual Commit	Projected Full Funding	Actual Full Funding
	5/27/2025		8/26/2025
9/15/2026		12/17/2026
9/15/2025			10/15/2025
	2/29/2025	3/30/2026

The result is the # of days between two dates, possibilities are:

Projected FFD – Projected Commit

Projected FFD – Actual Commit

Actual FFD – Projected Commit

Actual FFD – Actual Commit

[Ashish_Mathur]

Hi,

If there will only be 2 dates per row, then can we live with the log ic of max date - min date?

[Royel]

Hi @rosamhernandez1  lets create a calculated column to get the Days to funding 

Days To Funding = 
VAR CommitDate = COALESCE(data[Actual Commit], data[Projected Commit])
VAR FundingDate = COALESCE(data[Actual Full Funding], data[Projected Full Funding])
RETURN
    IF(
        NOT(ISBLANK(CommitDate)) && NOT(ISBLANK(FundingDate)),
        DATEDIFF(CommitDate, FundingDate, DAY),
        BLANK()
    )

Results: 

Find this helpful? ✔ Give a Kudo • Mark as Solution – help others too!

I’d love to stay connected. Join me on LinkedIn for more tips, learning paths, and real-world Fabric & Power BI solutions.

[danextian]

Hi @rosamhernandez1 

Assuming, there are only two dates per row and you're calculating the difference between the min and max dates, try this:

DateDiff =
VAR _Dates =
    FILTER (
        {
            'Table'[Projected Commit],
            'Table'[Actual Commit],
            'Table'[Projected Full Funding],
            'Table'[Actual Full Funding]
        },
        NOT ( ISBLANK ( [Value] ) )     // Keep only non-blank dates from this one-column table
        // The row constructor above creates a one-column, four-row table of the four date fields
    )
VAR _date1 =
    MINX ( _Dates, [Value] )            // Earliest available date
VAR _date2 =
    MAXX ( _Dates, [Value] )            // Latest available date
RETURN
    DATEDIFF ( _date1, _date2, DAY )     // Difference in days between earliest and latest

[KarinSzilagyi]

Hi @rosamhernandez1, am I understanding correctly that you just want to use the first non-null value of each actual vs projected date, then substract the two resulting dates?

In that case I would simplify the formula with coalesce().

Example:

Date Difference =
COALESCE([Projected Full Funding Date], [Actual Full Funding Date])
-
COALESCE([Projected Commit Gate Date], [Actual Commit Gate Date])

You can also simplify a nested IF() by using SWITCH()

[Jaywant-Thorat · Accepted solution]

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

[GeraldGEmerick]

@rosamhernandez1 You can nest if statements like the following:

if [Projected Commit Gate Date] <> null
then
[Projected Full Funding Date]-[Projected Commit Gate Date] else if [Actual Commit Gate Date] <> null then [Projected Full Funding Date]-[Actual Commit Gate Date] else <some other thing goes here>