목차
개요
우리가 분석하려고 하는 대부분의 데이터는 날짜 정보를 담고 있습니다. 그리고 이를 바탕으로 분석을 하려고 때, 그룹화 또는 계층화 (일, 월, 분기, 연) 할 수 있으면 특정 기간을 필터링해서 볼 수 있는 장점이 발생합니다.
Power BI는 자체적으로 시간 인텔리전스 기능을 제공하는데, 날짜 계층화를 자동으로 수행합니다. 이 기능으로 사용자가 간단한 작업을 수행하려고 할 때 아주 유용합니다.
단지 하나의 테이블만을 분석하려고 할때는 Power BI 시간 인텔리전스 기능으로 충분하지만, 다수의 테이블을 날짜를 기반으로 측정값을 비교하려고 할 때, 공통 캘린더 테이블을 만들어서 연결해야 합니다.
따라서 날짜 정보를 갖는 여러 데이터를 다룰 때, 다양하게 활용될 수 있는 캘린더 테이블을 만들어 사용하는 것이 유리합니다.
그래서, 많은 분들은 스스로 만든 캘린더 테이블을 가지고 있어서 활용하는데, 주로 아래와 같은 방법으로 생성합니다.
- 엑셀 파일로 만들어 Power BI에서 로딩
- 파워 쿼리 식으로 생성
- DAX CALENDAR 함수 식으로 생성
이번 포스트는 아주 간단한 형태, 그래서 DAX 시간 인텔리전스 함수 활용하는데 충분한, 캘린더 테이블을 만드는 DAX 식입니다. 식을 통해 아래와 같은 캘린더 테이블이 만들어집니다.
Power BI가 제공하는 시간 인텔리전스 기능은 아래 포스트를 참고하세요.
Power BI DAX 기초 - 시간 인텔리전스
목차 개요 날짜/시간 정보가 포함되어 있는 데이터를 분석하려고 할 때, 특정 기간 동안의 데이터를 효과적으로 계산하기 위해 Power BI Desktop은 “시간 인텔리전스” 기능을 제공합니다. 이
brightsomuch.tistory.com
캘린더 테이블에 연결할 연습용 데이터
아래는 연습을 위한 간단한 테이블 SHOP 입니다.
SHOP [O_Date] 열에는 주문 날짜가 있습니다. 이 날짜를 캘린더 테이블과 연결하려고 합니다.
캘린더 - 준비
캘린더 DAX 식을 만들기 전에 몇 가지 기본적인 DAX 함수를 둘러봅니다.
CALENDAR CALENDARAUTO
CALENDAR, CALENDARAUTO 함수는 모두 [Date] 열에 날짜 정보를 담고 있는 테이블을 만듭니다. 입력 파라미터로 시작과 마지막 날짜를 지정하는 방식이 약간 다릅니다.
CALENDAR 함수
연속된 날짜 집합이 포함된 "Date"라는 단일 열이 있는 테이블을 반환합니다. 날짜 범위는 지정된 시작 날짜부터 지정된 종료 날짜까지이며, 이 두 날짜를 포함합니다.
구문은 아래처럼 간단합니다.
- CALENDAR(<start_date>, <end_date>)
CALENDARAUTO 함수
연속된 날짜 집합이 포함된 ＂Date＂라는 단일 열이 있는 테이블을 반환합니다. 날짜 범위는 모델의 데이터를 기반으로 자동으로 계산됩니다.
구문은 아래 처럼 회계 연도 마지막 달 정보를 옵션으로 전달할 수 있습니다. 파라미터가 없을 경우 12월을 기준으로 처리합니다.
- CALENDARAUTO([fiscal_year_end_month])
ADDCOLUMNS
ADDCOLUMNS 함수로 CALENDAR 또는 CALENDARAUTO 함수로 만든 테이블 [Date] 열에서 날짜 정보를 바탕으로 다양한 종류로 카테고리화할 수 있는 열을 추가할 수 있습니다.
구문은 아래와 같습니다.
- ADDCOLUMNS(<table>, <name>, <expression>[, <name>, <expression>]…)
Calendar 테이블 생성
Calendar - DAX 식
위에서 언급한 DAX 함수를 이용해서 아래와 같이 Calendar 테이블을 만들 수 있습니다.
ADDCOLUMNS (
CALENDAR ( DATE ( 2020, 1, 1 ), DATE ( 2024, 12, 31 ) ),
//CALENDAR ( FIRSTDATE('SHOP'[O_Date]), LASTDATE('SHOP'[O_Date]) ),
//CALENDARAUTO(),
"Year", YEAR ( [Date] ),
"Quarter", "Q" & QUARTER ( [Date] ),
"Month Num", MONTH ( [Date] ),
"Week Num", WEEKNUM ( [Date] ),
"Week Day", WEEKDAY ( [Date] ),
"Day", DAY ( [Date] ),
"Month Name", FORMAT ( [Date], "mmmm" ),
"Week Name", FORMAT ( [Date], "dddd" )
)
이 DAX 식은 크게 두 부분으로 되어 있습니다.
- CALENDAR 또는 CALENDARAUTO를 통해 [Date] 열을 갖는 테이블 생성
- ADDCOLUMNS 파라미터로 열을 추가
Calendar - 날짜 구간 설정
[Date] 열을 생성할 때, 아래와 같은 방법으로 날짜 구간을 설정할 수 있습니다.
시작 날짜. 마지막 날짜 지정
CALENDAR 함수 입력 파라미터에 DATE 함수를 이용해서 시작 날짜와 마지막 날짜를 지정할 수 있습니다.
예) CALENDAR ( DATE(2020,1,1), DATE(2024,12,31) )
지정한 열 날짜 정보 기준
FIRSTDATE, LASTDATE에 날짜 정보를 담고 있는 열을 입력 파라미터로 제공해서, 해당 열의 처음 날짜와 마지막 날짜를 기반으로 Calendar를 만들 수 있습니다.
예) CALENDAR ( FIRSTDATE('SHOP'[O_Date]), LASTDATE('SHOP'[O_Date]) )
자동 계산
CALENDARAUTO 함수는 시작 날짜와 마지막 날짜가 모델의 데이터를 기반으로 자동으로 계산됩니다.
Calendar 테이블 날짜 연결
새로운 테이블이 만들어졌으니 모델 보기에서 테이블 SHOP, Calendar 테이블이 보입니다.
날짜 정보는 Calendar [Date] 기준이 되어야 하니, SHOP [O_Date]와 Calendar [Date]를 연결합니다.
Calendar [Date]를 마우스로 끌어와 SHOP [O_Date]에 놓으면, 새로운 관계를 맺을 수 있습니다.
새 관계 창이 나오는데, 자동으로 감지해 알맞은 설정값을 보여줍니다.
여기에서 유심히 봐야 하는 부분은 Cardinality입니다. Calendar 테이블은 모든 날짜가 유일합니다, 그러나 SHOP 테이블에는 동일 날짜가 있을 수 있죠.
그래서 일대다 (1:*) 관계가 되어 있습니다. 관계를 확인했으니 저장을 선택해서 마무리합니다.
그 결과 테이블 간 관계 설정이 되었다는 표시가 보이고, 마우스 포인트를 삼각형에 올리면 테이블간 연결되어 있는 필드가 표시됩니다.
마치며
두 테이블이 날짜로 서로 연결되어 있기 때문에, Calendar [Date]의 필터링으로 테이블 SHOP의 측정값 계산을 할 수 있습니다.
Calendar [Date] 아이콘을 보면 달력 표시가 되어있죠. 이는 Power BI의 시간 인텔리전스 기능에 의해 날짜 정보가 계층화되어 있습니다.
반대로 Calendar [Date]와 관계 설정 뒤, SHOP [O_Date] 달력 아이콘은 사라져 있습니다. 이는 SHOP [O_Date]는 계층화 정보가 아닌 열에 있는 날짜 정보라는 것을 나타냅니다.
날짜 데이터를 담고 있는 테이블을 분석할 때, 항상 Calendar 테이블을 만들어 사용합니다.
Calendar [Date]를 기준으로 필터 정보가 다른 테이블로 전파될 수 있고, DAX 시간 인텔리전스 함수 사용에 용이하기 때문입니다.
'Power BI' 카테고리의 다른 글
| Power BI DAX 기초 – DATESYTD 연도별 누적값 구하기 (3) | 2024.07.22 |
|---|---|
| Power BI DAX 기초 – 캘린더 이용 누적값 구하기 (0) | 2024.07.21 |
| Power BI 문자열 필터 기능 시각적 개체 Text Filter (0) | 2024.07.18 |
| Power BI DAX 기초 – 계산 열 키워드 찾기 SWITCH FILTER 활용 (0) | 2024.07.17 |
| Power BI DAX 기초 – IF SWITCH IN VAR 기초 동작 이해 (2) | 2024.07.16 |
