Power BI DAX 연중 기간 다양한 SAMEPERIODLASTYEAR 적용 방법

SAMEPERIODLASTYEAR는 Time Intelligence 기능 중에서 작년으로 기간을 옮겨서 데이터를 분석을 가능하게 하는 DAX 함수입니다. 연간 데이터가 종료가 되지 않았을 경우 다양하게 적용하는 방법에 대해서 정리하겠습니다.

 

결과

• ‘Sales’ 열 : 해당 기간 Sales 합계입니다.
• ‘Sales LY_1’열 : SAMEPERIODLASTYEAR를 단순 적용한 작년 Sales 합계입니다. 올해 Sales가 없는 기간에도 작년 Sales가 표시되고, 합계에도 작년 전체 기간 동안 Sales 합계가 나타납니다.
• ‘Sales LY_2’열 : 올해 Sales가 발생하지 않은 기간 동안 작년 Sales도 표시되지 않습니다.
• ‘Sales LY_3’열 : 2012년 합계에서 2011년 1월부터 8월까지 Sales 합계만 반영됩니다.
• ‘Sales LY_4’열 : 2011년과 2012년 합계가 공란으로 표시됩니다.

 

대시보드

 

측정식

Sales는 가격과 개수를 곱한 값들의 합을 구하는 측정식입니다.

Sales = 
SUMX(
    'Sales Table',
    'Sales Table'[Price] * 'Sales Table'[Units]

 

 

Sales LY_1은 SAMEPERIODLASTYEAR를 단순 적용한 측정식입니다.

Sales LY_1 = 
CALCULATE(
    [Sales],
    SAMEPERIODLASTYEAR('Calendar Table'[Date]))

 

 

Sales LY_2은 Sales 측정식에 공란이 없어야 SAMEPERIODLASTYEAR 함수를 적용하는 측정식입니다.

Sales LY_2 = 
IF( 
    [Sales] <> BLANK(),
    CALCULATE(
        [Sales],
        SAMEPERIODLASTYEAR('Calendar Table'[Date])
    )
)

 

 

• Sales LY_3은 2012년 총매출액에는 올해 실제 Sales가 발생한 기간의 1년 전 기간 동안의 Sales를 구하는 측정식입니다. 
• HASONEVALUE(‘Calendar Table’[Month])를 사용하여 테이블에 월이 있는 경우는 Sales LY_2와 동일한 측정식을 적용하고 그렇지 않은 경우에는 DATESBETWEEN에 설정된 기간 동안의 Sales 합계를 반영합니다. 
• DATEBETWEEN은 설정된 가간의 테이블을 반환하는 함수입니다. 
• EDATE는 시작 날짜에서 지정한 개월수 이전 또는 이후를 적용한 날짜를 반환하는 함수입니다. 
• EOMONTH는 지정한 개월수 이전 또는 이후 날짜의 마지막 날짜를 반환하는 함수입니다.

Sales LY_3 = 
IF(
    HASONEVALUE ( 'Calendar Table'[Month] ),
    IF ( 
        [Sales] <> BLANK(),
        CALCULATE (
            [Sales],
            SAMEPERIODLASTYEAR ( 'Calendar Table'[Date] )
        )
    ),
    CALCULATE (
        [Sales],
        DATESBETWEEN (
            'Calendar Table'[Date],
            EDATE ( MIN ( 'Calendar Table'[Date] ), -12 ),
            EOMONTH ( MAX ( 'Sales Table'[Date] ), -12 )
        )
    )
)

 

 

Sales LY_4는 전체 합계는 공란으로 처리하는 측정식입니다. 연도와 월 수준에 대해서만 계산을 수행하면 되므로 기존 측정값에서 DATEBETWEEN절에 다른 IF 절을 추가합니다.

Sales LY_4 = 
IF(
    HASONEVALUE ( 'Calendar Table'[Month] ),
    IF ( 
        [Sales] <> BLANK(),
        CALCULATE (
            [Sales],
            SAMEPERIODLASTYEAR ( 'Calendar Table'[Date] )
        )
    ),
    IF ( 
        HASONEVALUE ( 'Calendar Table'[Year] ),
            CALCULATE (
            [Sales],
            DATESBETWEEN (
                'Calendar Table'[Date],
                EDATE ( MIN ( 'Calendar Table'[Date] ), -12 ),
                EOMONTH ( MAX ( 'Sales Table'[Date] ), -12 )
            )
        )
    )
)

 

 

정리 소감

단순히 SAMEPERIODLASTYEAR를 적용하면 올해 데이터가 없는 기간에도 작년 데이터가 표출되거나 연도 수준 합계도 작년 총 합계가 반영됩니다. 보다 신뢰도 있는 분석을 하기 위해서 상황에 맞게 수정하는 방법을 배울 수 있어서 유용했습니다.

또한 위에서 정리되지는 않았지만 SAMEPERIODLASTYEAR가 DATEADD('Calendar Table'[Date], -1, YEAR)로 대체될 수 있음을 확인하였습니다.