Forum Discussion
Durbslaw
4 years agoHelper I
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...
- 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
4 years agoCommunity Champion
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
)
)