Forum Discussion

Durbslaw's avatar
Durbslaw
Helper I
4 years ago
Solved

conditional datediff

Hi,

 

I have 2 columns Period [YearMonth] which is a date filter and MovementSnapshot[AppointmentDatekey].

I would like a measure to datediff the Month if MovementSnapshot[AppointmentDatekey] is not blank or -1.

 

I have...

 

NA DateDiff = IF(NOT('MovementSnapshot'[AppointmentDateKey])= -1 && NOT(ISBLANK('MovementSnapshot'[AppointmentDateKey])) ,DATEDIFF(SELECTEDVALUE('Period'[YearMonth]),MAX('MovementSnapshot'[AppointmentDatekey]),MONTH))
 
Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

    Durbslaw 
    Please try this as a measure

     

    NewColumn =
IF (
    'MovementSnapshot'[AppointmentDateKey] <> BLANK ()
        && 'MovementSnapshot'[AppointmentDateKey] <> -1,
    VAR CurrentMovement =
        MAX ( 'MovementSnapshot'[AppointmentDateKey] )
    VAR CurrentPeriod =
        MAX ( 'Period'[YearMonth] )
    VAR MovementYear =
        VALUE ( LEFT ( CurrentMovement, 4 ) )
    VAR MovementMonth =
        VALUE ( MID ( CurrentMovement, 5, 2 ) )
    VAR PeriodYear =
        VALUE ( LEFT ( CurrentPeriod, 4 ) )
    VAR PeriodMonth =
        VALUE ( RIGHT ( CurrentPeriod, 2 ) )
    RETURN
        ( MovementYear - PeriodYear ) * 12 + MovementMonth - PeriodMonth
)

     

  • tamerj1's avatar
    tamerj1
    4 years ago

    Durbslaw 
    Sorry my mistake. Please try

     

    NewColumn =
VAR CurrentMovement =
    MAX ( 'MovementSnapshot'[AppointmentDateKey] )
VAR CurrentPeriod =
    MAX ( 'Period'[YearMonth] )
VAR MovementYear =
    VALUE ( LEFT ( CurrentMovement, 4 ) )
VAR MovementMonth =
    VALUE ( MID ( CurrentMovement, 5, 2 ) )
VAR PeriodYear =
    VALUE ( LEFT ( CurrentPeriod, 4 ) )
VAR PeriodMonth =
    VALUE ( RIGHT ( CurrentPeriod, 2 ) )
RETURN
    IF (
        CurrentMovement <> BLANK ()
            && CurrentMovement <> -1,
        ( MovementYear - PeriodYear ) * 12 + MovementMonth - PeriodMonth
    )

     

11 Replies

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        Hi Durbslaw 
        Please use the following formula

        NewColumn =
IF (
    'MovementSnapshot'[AppointmentDateKey] <> BLANK ()
        && 'MovementSnapshot'[AppointmentDateKey] <> -1,
    VAR MovementYear =
        VALUE ( LEFT ( 'MovementSnapshot'[AppointmentDateKey], 4 ) )
    VAR MovementMonth =
        VALUE ( MID ( 'MovementSnapshot'[AppointmentDateKey], 4, 2 ) )
    VAR PeriodYear =
        VALUE ( LEFT ( 'Period'[YearMonth], 4 ) )
    VAR PeriodMonth =
        VALUE ( RIGHT ( 'Period'[YearMonth], 2 ) )
    RETURN
        ( MovementYear - PeriodYear ) * 12 + MovementMonth - PeriodMonth
)
  • PC2790's avatar
    PC2790
    Community Champion
    4 years ago

    Hey Durbslaw ,

     

    Is it not giving you right result?

    What is your end requirement?

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        Durbslaw 

        The location of the closing bracket of not should be after the "1". But even though I don't believe this is a correct code. Please provide more details about your data. 

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Durbslaw 
    Please try this as a measure

     

    NewColumn =
IF (
    'MovementSnapshot'[AppointmentDateKey] <> BLANK ()
        && 'MovementSnapshot'[AppointmentDateKey] <> -1,
    VAR CurrentMovement =
        MAX ( 'MovementSnapshot'[AppointmentDateKey] )
    VAR CurrentPeriod =
        MAX ( 'Period'[YearMonth] )
    VAR MovementYear =
        VALUE ( LEFT ( CurrentMovement, 4 ) )
    VAR MovementMonth =
        VALUE ( MID ( CurrentMovement, 5, 2 ) )
    VAR PeriodYear =
        VALUE ( LEFT ( CurrentPeriod, 4 ) )
    VAR PeriodMonth =
        VALUE ( RIGHT ( CurrentPeriod, 2 ) )
    RETURN
        ( MovementYear - PeriodYear ) * 12 + MovementMonth - PeriodMonth
)

     

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        Durbslaw 
        Sorry my mistake. Please try

         

        NewColumn =
VAR CurrentMovement =
    MAX ( 'MovementSnapshot'[AppointmentDateKey] )
VAR CurrentPeriod =
    MAX ( 'Period'[YearMonth] )
VAR MovementYear =
    VALUE ( LEFT ( CurrentMovement, 4 ) )
VAR MovementMonth =
    VALUE ( MID ( CurrentMovement, 5, 2 ) )
VAR PeriodYear =
    VALUE ( LEFT ( CurrentPeriod, 4 ) )
VAR PeriodMonth =
    VALUE ( RIGHT ( CurrentPeriod, 2 ) )
RETURN
    IF (
        CurrentMovement <> BLANK ()
            && CurrentMovement <> -1,
        ( MovementYear - PeriodYear ) * 12 + MovementMonth - PeriodMonth
    )