Power BI 시간 인텔리전스 사용을 위한 날짜 테이블 만들기 (CALENDAR, CALENDARAUTO)

시간 인텔리전스는 Power BI DAX에서 매우 중요하고 강력한 기능입니다. 시간 인텔리전스 중에서 가장 기본이면서 출발점이 되는 CALENDAR와 CALENDARAUTO를 사용한 날짜 테이블 만들기에 대해서 살펴보겠습니다.

 

배경

Power BI에서 시간 인텔리전스를 사용하면 행렬의 날짜 선택을 변경하지 않아도 상대적인 날짜의 측정값을 생성할 수 있습니다. 내장 시간 인텔리전스를 사용하기 위해서는 두 가지 규칙을 만족해야 합니다.

• 분석 대상이 되는 모든 날짜 범위를 포함하는 Calendar 테이블이 있어야 합니다. 어떤 날짜도 빠져서는 안되고 모든 날짜는 Calendar 테이블에 한 번만 존재해야 합니다.
• 시간 인텔리전스는 표준 달력에서 작동합니다. 표준 달력이라 함은 우리가 일반적으로 사용하는 달력을 의미합니다. 회계 연도는 설정할 수 있는 기능이 있습니다.

 

Power BI에서는 자동 날짜/시간 기능을 지원합니다. 하지만 날짜 열이 있는 모든 테이블에 시간 인텔리전스와 같은 동작을 자동으로 생성하게 되므로 데이터 모델이 커지고 속도도 느려지는 단점이 있습니다. 따라서 전용 Calendar 테이블을 생성 후 연결하여 사용하는 방법을 추천드립니다. 전용 Calendar를 생성하는 함수가 CALENDAR와 CALENDARAUTO입니다. 

 

참고로 자동 날짜 시간 기능을 해제하는 방법은 아래와 같습니다.

1. [파일] – [옵션 및 설정] – [옵션]을 선택합니다.
2. [현재 파일]에서 [데이터 로드]를 선택합니다.
3. 자동 날짜 시간을 선택 해제합니다.

 

CALENDAR, CALENDARAUTO 사용법

CALENDAR

CALENDAR 함수는 지정한 시작 날짜부터 종료 날짜까지의 모든 날짜를 포함한 연속된 날짜 집합을 ‘Date’라는 단일 열이 있는 테이블로 반환합니다. CALENDAR 함수는 아래와 같은 형태로 작성합니다. 시작 날짜와 종료 날짜는 날짜/시간 값을 반환하는 DAX식입니다.

날짜 테이블 = CALENDAR(시작 날짜, 종료 날짜)

시작 날짜와 종료 날짜는 직접 지정할 수도 있고, MIN 또는 MAX함수를 이용하여 정의할 수도 있습니다.

/* 예제1 – 날짜 직접 입력하여 생성 */
날짜 테이블 = CALENDAR(DATE(2022,01,01), DATE(2022.03,17))

/* 예제2 – 데이터 테이블 참조하여 생성 */
날짜 테이블 =
CALENDAR ( MINX ( Sales, [Date] ), MAXX ( Forecast, [Date] ) )

/* 예제3 – 데이터 테이블 참조하여 생성 */
날짜 테이블 =
CALENDAR (
    DATE ( YEAR ( MIN ( 'Sales'[Date] ) ), 1, 1 ),
    DATE ( YEAR ( MAX ( 'Sales'[Date] ) ), 12, 31 )
)

 

CALENDARAUTO

CALENDARAUTO는 회계연도 기준으로 연속된 날짜 집합이 포함된 ‘Date’라는 단일 열이 있는 테이블을 반환합니다. 날짜 범위는 모델 안에서 날짜형 데이터를 기준으로 자동으로 계산됩니다. CALENDARAUTO 함수는 아래와 같은 형태로 작성합니다. 괄호 안에는 회계연도 종료 월을 적어줍니다. 괄호 안을 비워두면 1월 1일부터 날짜 범위가 지정됩니다.

날짜 테이블 = CALENDARAUTO(회계연도 종료 월)

회계연도 종료 월을 직접 입력하여 생성할 수도 있고, MIN 또는 MAX함수를 이용하여 생성할 수도 있습니다.

/* 예제1 – 회계연도 종료 월 직접 입력하여 생성 */
날짜 테이블 = CALENDARAUTO(6)

/* 예제2 – 데이터 테이블 참조하여 생성 */
날짜 테이블 =
VAR MinYear =
    YEAR ( MIN ( 'Sales by Store'[Transaction Date] ) )
VAR MaxYear =
    YEAR ( MAX ( 'Sales by Store'[Transaction Date] ) )
RETURN
    FILTER (
        CALENDARAUTO (),
        YEAR ( [Date] ) >= MinYear
            && YEAR ( [Date] ) <= MaxYear
    )

 

CALENDARAUTO 템플렛 만들기

ADDCOLUMN 함수와 날짜 형식을 이용하여 연도, 분기, 월 등의 열을 추가할 수 있습니다. 매번 CALENDAR와 CALENDARAUTO 기능을 확인하여 날짜 테이블을 만들면 번거롭습니다. 아래 예제 코드를 템플렛으로 참조하여 필요할 때마다 복사 후 'Calendar'[Transaction_Date] 부분만 수정해서 쓰면 효율적입니다.

Date Table (CALENDARAUTO) =
VAR MinYear =
    YEAR ( MIN ( 'Calendar'[Transaction_Date] ) )
VAR MaxYear =
    YEAR ( MAX ( 'Calendar'[Transaction_Date] ) )
RETURN
    ADDCOLUMNS (
        FILTER (
            CALENDARAUTO (),
            YEAR ( [Date] ) >= MinYear
                && YEAR ( [Date] ) <= MaxYear
        ),
        "Year", YEAR ( [Date] ),
        "Quarter Number", INT ( FORMAT ( [Date], "q" ) ),
        "Quarter", "Q" & INT ( FORMAT ( [Date], "q" ) ),
        "Month Number", MONTH ( [Date] ),
        "Month Short", FORMAT ( [Date], "mmm" ),
        "Week Number", WEEKNUM ( [Date] )
    )

 

날짜 포매팅 (Date Formatting)

Date열의 날짜를 여러 가지 형식으로 표현할 수 있습니다. 아래 테이블을 참조하여 원하는 형식으로 날짜를 표현하시기 바랍니다.

Code	예
dddd	Sunday, Monday
ddd	Sun, Mon
mmmm	July, August
mmm	Jul, Aug
mm	08, 09, 10, 11
q	1, 2, 3,4
yyyy	2018, 2019, 2020

 

정리 소감

CALENDAR 또는 CALENDARAUTO DAX 함수를 사용하면 Power BI에서 날짜 테이블을 쉽게 생성할 수 있습니다. 날짜 테이블을 생성 후에는 데이터 테이블의 날짜 열과 연결이 필요합니다. 날짜 테이블을 활용하는 것이 자동 날짜 시간 기능을 사용하는 것보다 더욱 빠르고 파일 크기도 작아지는 것을 체감할 수 있었습니다. 이제 시간 인텔리전스를 사용할 기본적인 셋업이 끝난 것 같습니다. 시간 인텔리전스를 사용하여 일 년 전 매출, YTD 등의 측정값을 쉽게 구할 수 있는데 이 부분은 추후에 정리해보도록 하겠습니다.

CALENDAR