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('M...
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.

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