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

     

PC2790's avatar
PC2790
Community Champion
4 years ago

Hey Durbslaw ,

 

Is it not giving you right result?

What is your end requirement?

  • Durbslaw's avatar
    Durbslaw
    Helper I
    4 years ago

    error

    • 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. 

    • PC2790's avatar
      PC2790
      Community Champion
      4 years ago

      This will not work as a measure. Try having a calculated column. Something like:

      Column = if(Not(ISBLANK('MovementSnapshot'[AppointmentDateKey])) || 'MovementSnapshot'[AppointmentDateKey] <> -1,DATEDIFF('Period'[YearMonth],'MovementSnapshot'[AppointmentDateKey],MONTH))
       
      Also, another thing to make sure is the two values you are using are of date datatype.Otherwise it won't work like this.