Forum Discussion

Helper I

8 months ago

Solved

Complicated IF

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

Need Help

Jaywant-Thorat
8 months ago
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

6 Replies

GeraldGEmerick

Super User

8 months ago

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>

Jaywant-Thorat
Super User
8 months ago
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
KarinSzilagyi
Super User
8 months ago
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()

danextian

Super User

8 months ago

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

Royel

Super User

8 months ago

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.

Ashish_Mathur
Super User
8 months ago
Hi,

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

Forum Discussion

Complicated IF

6 Replies

Related content

Extracting complicated text from a column

Complicated unpivot

Complicated Filtering

Complicated Financial Reporting in Power BI

HELP! complicated grouped line chart