Summarize from multiple tables

[Durbslaw]

Hello Forum,

TIA for reading and responding, I am using SSAS as a source, and have created this measure to calculate month difference between 2 dates

New Hires Month Datediff =
IF (
MAX('MovementSnapshot'[AppointmentDateKey]) <> BLANK ()
&& MAX('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
( PeriodYear - MovementYear ) * 12 + PeriodMonth - MovementMonth
)

The issue comes when I am trying to count the number of 0 and 1 month occurrences.

CountNewHires =
COUNTROWS(
FILTER(
SUMMARIZE(
'Period',
'Period'[Yearmonth],
RelatedTable(''MovementSnapshot'[AppointmentDateKey]),
"Value" , [New Hires Month Datediff]
),
[Value] = 0 || [Value] = 1
))

The syntax error is attached.

[tamerj1 · Accepted solution]

Hi @Durbslaw 

please use the following 

New Hires Month Datediff =
VAR CurrentMovement =
    MAX ( 'MovementSnapshot'[AppointmentDateKey] )
RETURN
    IF (
        CurrentMovement <> BLANK ()
            && CurrentMovement <> -1,
        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
            ( PeriodYear - MovementYear ) * 12 + PeriodMonth - MovementMonth
    )

CountNewHires =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE ( 'MovementSnapshot', 'Period'[Yearmonth] ),
            "Value", [New Hires Month Datediff]
        ),
        [Value] = 0
            || [Value] = 1
    )
)

[tamerj1 · Accepted solution]

Hi @Durbslaw 

please use the following 

New Hires Month Datediff =
VAR CurrentMovement =
    MAX ( 'MovementSnapshot'[AppointmentDateKey] )
RETURN
    IF (
        CurrentMovement <> BLANK ()
            && CurrentMovement <> -1,
        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
            ( PeriodYear - MovementYear ) * 12 + PeriodMonth - MovementMonth
    )

CountNewHires =
COUNTROWS (
    FILTER (
        ADDCOLUMNS (
            SUMMARIZE ( 'MovementSnapshot', 'Period'[Yearmonth] ),
            "Value", [New Hires Month Datediff]
        ),
        [Value] = 0
            || [Value] = 1
    )
)

[Durbslaw]

@tamerj1  Thank you very much however still getting..

[tamerj1 · Accepted solution]

@Durbslaw 
I think this is the time when you need to add some columns and create a relatioship between the two tables.

[Durbslaw]

I have also tried...

CountNewHires =
COUNTROWS(
FILTER(
SUMMARIZE(
'Period',
'Period'[Yearmonth],
'MovementSnapshot'[AppointmentDateKey],
CALCULATETABLE ( VALUES ('Staff Management' [New Hires Month Datediff] ) )
),
[Value] = 0 || [Value] = 1
))

AND

CountNewHires =
COUNTROWS(
FILTER(
SUMMARIZE(
'Period',
'Period'[Yearmonth],
CALCULATETABLE ('MovementSnapshot'[AppointmentDateKey],
Value , [New Hires Month Datediff]
),
[Value] = 0 || [Value] = 1
))