Desperately need help with DATEDIFF calculation

[pbi_qwerty]

Hi, I have a table like this:

Vacancy Vacancy No.	Job Source Sourcing channel	Vacancy Source closing date Date	Vacancy Source Opening Date Date	Vacancy Date created Date	Vacancy Date approved Date	Vacancy Date filled Date
891925	A	31/01/2025	09/01/2025	02/01/2025	06/01/2025	15/04/2025
891925	B	31/01/2025	09/01/2025	02/01/2025	06/01/2025	15/04/2025
891925	C	31/01/2025	09/01/2025	02/01/2025	06/01/2025	15/04/2025
891926	A	01/02/2025	10/01/2025	02/01/2025	06/01/2025	18/02/2025
891926	B	01/02/2025	10/01/2025	02/01/2025	06/01/2025	18/02/2025
891926	C	01/02/2025	10/01/2025	02/01/2025	06/01/2025	18/02/2025
891926	D	01/02/2025	10/01/2025	02/01/2025	06/01/2025	18/02/2025
891926	E	01/02/2025	10/01/2025	02/01/2025	06/01/2025	18/02/2025
891935	A	15/01/2025	03/01/2025	02/01/2025	02/01/2025	30/01/2025
891935	B	15/01/2025	03/01/2025	02/01/2025	02/01/2025	30/01/2025
891935	C	15/01/2025	03/01/2025	02/01/2025	02/01/2025	30/01/2025
891935	D	15/01/2025	03/01/2025	02/01/2025	02/01/2025	30/01/2025

Dates are in Australia format. Dates are all identical for each date column per vacancy.

I need to get a singular DATEDIFF result (in DAYS) per vacancy between Vacancy Created Date and Vacancy Source Opening Date, for example.

I've been pulling my hair out. Have tried creating an Index column, no luck. Eventually, my final destination is to get an average in days between each date column for each vacancy.

I am using the Job Source Channel column in other visualisations so can't delete it (thus can't delete duplicate vacancy number rows).

Basic DATEDIFF function:

_Days From Vacancy Created Date to Job Source Opening Date =
    DATEDIFF('PageUp_JobSourceData_Consolidated'[Vacancy Date created Date], 'PageUp_JobSourceData_Consolidated'[Vacancy Source Opening Date Date], DAY)

Gives this result:

Vacancy Vacancy No.	Vacancy Source Opening Date Date	Vacancy Date created Date	_Days From Vacancy Created Date to Job Source Opening Date
891925	09/01/2025	02/01/2025	7
891925	09/01/2025	02/01/2025	7
891925	09/01/2025	02/01/2025	7
891926	10/01/2025	02/01/2025	8
891926	10/01/2025	02/01/2025	8
891926	10/01/2025	02/01/2025	8
891926	10/01/2025	02/01/2025	8
891926	10/01/2025	02/01/2025	8
891935	03/01/2025	02/01/2025	1
891935	03/01/2025	02/01/2025	1
891935	03/01/2025	02/01/2025	1
891935	03/01/2025	02/01/2025	1

I need the calculated column (_Days From Vacancy Created Date to Job Source Opening Date) to only populate one cell per vacancy in its column, not every cell. That way I won't be counting the duplicates when I do my average days result at the end.

Or, if there's another method I'd love to hear it.

Any help would be greatly appreciated! Cheers.

[Bibiano_Geraldo · Accepted solution]

Hi @pbi_qwerty ,

Please, add new calculated column using this DAX:

DaysCreatedToOpening = 
VAR ThisVacancy     = 'PageUp_JobSourceData_Consolidated'[Vacancy Vacancy No.]
VAR ThisJobSource   = 'PageUp_JobSourceData_Consolidated'[Job Source Sourcing channel]
VAR MinJobSourceForVacancy =
    CALCULATE(
        MIN( 'PageUp_JobSourceData_Consolidated'[Job Source Sourcing channel] ),
        ALLEXCEPT(
            'PageUp_JobSourceData_Consolidated',
            'PageUp_JobSourceData_Consolidated'[Vacancy Vacancy No.]
        )
    )
RETURN
IF(
    ThisJobSource = MinJobSourceForVacancy,
    DATEDIFF(
        'PageUp_JobSourceData_Consolidated'[Vacancy Date created Date],
        'PageUp_JobSourceData_Consolidated'[Vacancy Source Opening Date Date],
        DAY
    ),
    BLANK()
)

Your finally output will look like this:

[pbi_qwerty]

Works great, thank you!

[Nasif_Azam]

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

[pbi_qwerty]

Thank you, I'll look at your solution soon, as I want to learn different ways of doing this. I have marked a different response as an accepted solution.

[ajaybabuinturi]

Hi @pbi_qwerty ,

Please try with below measure and calculated column

_Days From Vacancy Created Date to Job Source Opening Date = 
AVERAGEX(
    VALUES('PageUp_JobSourceData_Consolidated'[Vacancy Vacancy No.]), //If Number is text
    DATEDIFF(
        MINX(FILTER('PageUp_JobSourceData_Consolidated', 
					'PageUp_JobSourceData_Consolidated'[Vacancy Vacancy No.] = EARLIER('PageUp_JobSourceData_Consolidated'[Vacancy Vacancy No.])),
            'PageUp_JobSourceData_Consolidated'[Vacancy Date created Date]
			),
        MINX(
            FILTER('PageUp_JobSourceData_Consolidated',
				   'PageUp_JobSourceData_Consolidated'[Vacancy Vacancy No.] = EARLIER('PageUp_JobSourceData_Consolidated'[Vacancy Vacancy No.])),
            'PageUp_JobSourceData_Consolidated'[Vacancy Source Opening Date Date]
			),
        DAY
    )
)

//Caculated column
_Days From Vacancy Created Date to Job Source Opening Date2 = 
VAR CurrentVacancy = 'PageUp_JobSourceData_Consolidated'[Vacancy Vacancy No.]
VAR CreatedDate =
    CALCULATE(MIN('PageUp_JobSourceData_Consolidated'[Vacancy Date created Date]),
        ALLEXCEPT('PageUp_JobSourceData_Consolidated', 'PageUp_JobSourceData_Consolidated'[Vacancy Vacancy No.]))
VAR SourceOpenDate =
    CALCULATE(MIN('PageUp_JobSourceData_Consolidated'[Vacancy Source Opening Date Date]),
        ALLEXCEPT('PageUp_JobSourceData_Consolidated', 'PageUp_JobSourceData_Consolidated'[Vacancy Vacancy No.]))
VAR RowNumber =
    RANKX(
        FILTER(
            'PageUp_JobSourceData_Consolidated',
            'PageUp_JobSourceData_Consolidated'[Vacancy Vacancy No.] = CurrentVacancy),
        'PageUp_JobSourceData_Consolidated'[Job SourceSourcing channel], , ASC, DENSE)
RETURN
    IF(RowNumber = MAXX(
        FILTER('PageUp_JobSourceData_Consolidated', 'PageUp_JobSourceData_Consolidated'[Vacancy Vacancy No.] = CurrentVacancy),
        RANKX(
            FILTER(
                'PageUp_JobSourceData_Consolidated',
                'PageUp_JobSourceData_Consolidated'[Vacancy Vacancy No.] = CurrentVacancy),
            'PageUp_JobSourceData_Consolidated'[Job SourceSourcing channel], , ASC, DENSE)
    ),
    DATEDIFF(CreatedDate, SourceOpenDate, DAY)
)

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.

[pbi_qwerty]

Thanks so much, I'll view your solution soon. I have marked a separate response as the solution (even though I'm sure yours will be also!).