Forum Discussion

pbi_qwerty's avatar
pbi_qwerty
New Member
1 year ago
Solved

Desperately need help with DATEDIFF calculation

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 D...
Need Help
  • Bibiano_Geraldo's avatar
    Bibiano_Geraldo
    1 year ago

    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:

ajaybabuinturi's avatar
ajaybabuinturi
Icon for Super User rankSuper User
1 year ago

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's avatar
pbi_qwerty
New Member
1 year ago

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!).