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
    )

     

tamerj1's avatar
tamerj1
Community Champion
4 years ago

Durbslaw 

How does the data in each column look like?

  • Durbslaw's avatar
    Durbslaw
    Helper I
    4 years ago

    • 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
)
      • Durbslaw's avatar
        Durbslaw
        Helper I
        4 years ago

        Aplogies for the non clarity, I need a measure as I am using SSAS as a source and cannot create columns.