conditional datediff

[Durbslaw]

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

[tamerj1 · Accepted solution]

@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 · Accepted solution]

@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
)

[Durbslaw]

syntax error...

[tamerj1 · Accepted solution]

@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]

Hey @Durbslaw ,

Is it not giving you right result?

What is your end requirement?

[Durbslaw]

error

[PC2790]

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.

[tamerj1]

@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]

@Durbslaw 

How does the data in each column look like?

[Durbslaw]

[tamerj1]

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]

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