Forum Discussion

android1's avatar
android1
Icon for Post Patron rankPost Patron
8 years ago
Solved

DATEDIFF with a condition

Hi,   How would I modify the below to give me Weeks of Employment in one column? I want to use Weeks of  Employement 1 but if they have a [DateLeft] then I need to use Weeks of Employment 2   We...
Need Help
  • v-yulgu-msft's avatar
    v-yulgu-msft
    8 years ago

    Hi android1,

     

    Suppose there existing a relationship between 'vw_PivotVisitsInvoiced' and 'CarersList' based on employee ID. If so, you can use below formula to add the DateLeft into 'vw_PivotVisitsInvoiced'.

    CarerLastDate = RELATED(CarersList[DateLeft])

     

    Then, create the following calculated column:

     

    Weeks of Employment= IF([CarerLastDate]=BLANK(),

                           DATEDIFF(vw_PivotVisitsInvoiced[Carer_StartDate],TODAY(),WEEK),

                           DATEDIFF(

                               FIRSTDATE(vw_PivotVisitsInvoiced[Carer_StartDate]),

                               LASTDATE(CarersList[DateLeft]) ,

                               WEEK

))

    Best regards,
    Yuliana Gu

v-yulgu-msft's avatar
v-yulgu-msft
Icon for Microsoft Employee rankMicrosoft Employee
8 years ago

Hi android1,

 

Suppose there existing a relationship between 'vw_PivotVisitsInvoiced' and 'CarersList' based on employee ID. If so, you can use below formula to add the DateLeft into 'vw_PivotVisitsInvoiced'.

CarerLastDate = RELATED(CarersList[DateLeft])

 

Then, create the following calculated column:

 

Weeks of Employment= IF([CarerLastDate]=BLANK(),

                           DATEDIFF(vw_PivotVisitsInvoiced[Carer_StartDate],TODAY(),WEEK),

                           DATEDIFF(

                               FIRSTDATE(vw_PivotVisitsInvoiced[Carer_StartDate]),

                               LASTDATE(CarersList[DateLeft]) ,

                               WEEK

))

Best regards,
Yuliana Gu