Forum Discussion

Durbslaw's avatar
Durbslaw
Helper I
4 years ago
Solved

Summarize from multiple tables

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.

Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

    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's avatar
    tamerj1
    4 years ago

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

4 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    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's avatar
        tamerj1
        Community Champion
        4 years ago

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

  • Durbslaw's avatar
    Durbslaw
    Helper I
    4 years ago

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