Desperately need help with DATEDIFF calculation

Hey pbi_qwerty ,

You're very close! You're correctly using DATEDIFF, but the issue is with how the result is repeated for every row per vacancy, leading to duplicate counts when calculating the average. Let’s walk through a solution that will give you a single DATEDIFF result per vacancy without removing the rows (so your visualizations using Job Source Channel stay intact), and then how to calculate the average properly.

Objectives:

• Calculate difference between [Vacancy Date created Date] and [Vacancy Source Opening Date Date].

• Ensure it's only calculated once per Vacancy (not per row).

• Keep all rows to retain Job Source Sourcing channel context.

• Be able to calculate an average of these differences per vacancy.

Solution (Power BI - DAX)

Step 1: Create a summarized table (optional but recommended)

If you want to visualize only unique vacancy-level DATEDIFFs, create a summary table:

Vacancy Date Diff Summary =
SUMMARIZE(
    'PageUp_JobSourceData_Consolidated',
    'PageUp_JobSourceData_Consolidated'[Vacancy Vacancy No.],
    "Days CreatedToOpen", 
        DATEDIFF(
            MIN('PageUp_JobSourceData_Consolidated'[Vacancy Date created Date]),
            MIN('PageUp_JobSourceData_Consolidated'[Vacancy Source Opening Date Date]),
            DAY
        )
)

Step 2: Create a calculated column to mark only one row per vacancy

In your main table:

Is First Row Per Vacancy =
VAR CurrentVacancy = 'PageUp_JobSourceData_Consolidated'[Vacancy Vacancy No.]
VAR MinDate = 
    CALCULATE(
        MIN('PageUp_JobSourceData_Consolidated'[Vacancy Source Sourcing channel]),
        FILTER(
            'PageUp_JobSourceData_Consolidated',
            'PageUp_JobSourceData_Consolidated'[Vacancy Vacancy No.] = CurrentVacancy
        )
    )
RETURN
    'PageUp_JobSourceData_Consolidated'[Vacancy Source Sourcing channel] = MinDate

This helps you keep only one record "active" per vacancy for DATEDIFF.

Step 3: Add a new calculated column for DATEDIFF with that condition

Days From Created to Open (Single Row) =
VAR IsFirst = 'PageUp_JobSourceData_Consolidated'[Is First Row Per Vacancy]
RETURN
IF(
    IsFirst,
    DATEDIFF(
        'PageUp_JobSourceData_Consolidated'[Vacancy Date created Date],
        'PageUp_JobSourceData_Consolidated'[Vacancy Source Opening Date Date],
        DAY
    ),
    BLANK()
)

This way, only one row per vacancy has a number; the others will be blank.

Step-4: Getting the Average

Now, when building your average visual or metric, use:

Average Days Created to Open =
AVERAGEX(
    VALUES('PageUp_JobSourceData_Consolidated'[Vacancy Vacancy No.]),
    CALCULATE(
        DATEDIFF(
            MIN('PageUp_JobSourceData_Consolidated'[Vacancy Date created Date]),
            MIN('PageUp_JobSourceData_Consolidated'[Vacancy Source Opening Date Date]),
            DAY
        )
    )
)

This gives the correct average without being affected by the number of sourcing channels per vacancy.

Result Will Be:

• No duplicate counts.

• Keeps all job source rows for visual use.

• Accurate averages per vacancy.

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam