목차
개요
Power BI 시간 인텔리전스 기능과 연속된 모든 날짜를 담고 있는 Calendar 테이블이 준비되어 있다면, 날짜 정보를 갖고 있는 데이터를 분석할 준비가 되었습니다.
아래 포스트에 날짜 정보를 담고 있는 데이터를 DAX 식으로 분석하기 전에 이해하고 있어야 할 부분을 다루었으니 꼭 참고하세요.
Power BI DAX 기초 - 캘린더 만들기
목차 개요 우리가 분석하려고 하는 대부분의 데이터는 날짜 정보를 담고 있습니다. 그리고 이를 바탕으로 분석을 하려고 때, 그룹화 또는 계층화 (일, 월, 분기, 연) 할 수 있으면 특정 기간을
brightsomuch.tistory.com
이번 포스트는 날짜 정보를 다루는 가장 기본적인 부분으로 누적값을 구하는 방법을 통해 캘린더 테이블이 어떻게 활용되는지 살펴보도록 하겠습니다.
캘린더 테이블과 예제 데이터
SHOP 테이블의 SHOP [O_Date]는 주문 날짜를 담고 있습니다. 그리고 Amount의 합을 계산하는 측정값 [Sum of Amount]는 아래와 같이 정의합니다.
Sum of Amount =
SUM ( SHOP[Amount] )
Calendar 테이블은 DAX 식으로 만들었으며, 아래처럼 날짜를 서로 연결했습니다.
CALENDARAUTO 함수를 사용했기 때문에, Calendar [Date]는 2023년 1월 1일부터 2024년 12월 31일까지 날짜 정보를 담고 있습니다.
Power BI 시간 인텔리전스
날짜를 다루기에 앞서 Power BI 시간 인텔리전스 기능을 잠깐 둘러보고 갈게요.
Power BI 시간 인텔리전트 기능에 의해 Calendar [Date] 열은 자동으로 계층화되고, 시각적 개체 테이블 또는
누적 세로 막대형 차트로 [Sum of Amount]를 표현할 수 있습니다.
X축으로 Calendar [Date]열을 사용했으며, 연/월을 선택해서 측정값 [Sum of Amount]를 구했습니다.
한 가지 유심히 봐야 하는 것이, 2023년 3월처럼 [Sum of Amount] 값이 없습니다.
이는 SHOP [O_Date] 열 정보에 해당 월에 아무런 내용이 없기 때문이죠.
누적 세로 막대형 차트로 그릴 경우, [Sum of Amount] 값이 있는 월만 표현하기 때문에, 매월 간격을 균등하게 만들어야 한다면, 아래처럼 조정을 해 줘야 합니다.
시각화 개체의 시각화에서 Date, 데이터가 없는 항목 표시를 선택하면 아래처럼 측정값 [Sum of Amount]가 없는 월에 대해서도 그래프로 나타낼 수 있습니다.
X축은 Calendar [Date]으로 만들어지기 때문에, 2023년 1월부터 2024년 12월까지 모두 표시됩니다. 슬라이서 또는 필터를 통해 조절은 가능합니다.
누적값 구하기 - 방법
앞서 설명했듯이 시간 인텔리전트 기능으로 Calendar [Date] 계층 단계별로 [Sum of Amount]를 구할 수 있었습니다.
누적값을 구하기 위해서는 계속 더할 수 있는 DAX 식이 필요합니다. 이는 시작 날짜부터 계산이 되는 날짜까지 모두 포함하는 날짜 테이블을 만들고, 그곳에서 Amount를 합하면 됩니다.
따라서 아래와 같이 필요한 DAX 함수를 생각할 수 있습니다.
- 날짜 테이블을 만든다 --> FILTER 함수 또는 시간 인텔리전스 함수
- 만들어진 테이블에서 측정값을 계산한다 --> CALCULATE 함수
- 필터 컨텍스트 기준으로 마지막 날짜 --> MAX 함수
MAX 함수
MAX 함수가 캘린더 테이블과 함께 사용될 때, 어떻게 활용될 수 있는지 이해가 중요합니다.
MAX 함수는 아래 페이지에 기술되어 있으며, 해당 열에서 가장 큰 값을 반환합니다.
구분은 아래처럼 간단합니다.
- MAX(<column>)
이해를 위해서 아래 두 식이 어떤 의미를 갖는지 이해해야 합니다.
- MAX(SHOP[O_Date]): 필터 컨텍스트의 날짜 구간에 포함되는 SHOP [O_Date] 중에서 최댓값.
- MAX(Calendar[Date]): 필터 컨텍스트의 날짜 구간에 포함되는 Calendar [O_Date] 중에서 최댓값. Calendar는 모든 날짜를 담고 있기 때문에, 그 구간에서 항상 마지막 날짜가 됩니다.
측정값 [MAX O_Date] = MAX(SHOP [O_Date])을 만들어 확인해 볼 수 있습니다.
MAX O_Date =
MAX ( SHOP[O_Date] )
Year/Month를 갖는 테이블과, Year/Quarter를 갖는 테이블입니다. 측정값 [MAX O_Date]은 각 구간의 O_Date 중에서 가장 큰 값을 나타내고 있습니다.
MAX 함수의 특성으로 누적값 계산에 반드시 필요합니다.
누적값 DAX 식 - 데이터 날짜 기준
누적값 계산을 위한 DAX 식을 아래와 같이 정의를 해 보겠습니다.
식의 의미:
- FILTER 함수에 의해 현재 컨텍스트에 포함된 SHOP [O_Date]의 최대 날짜 이전의 모든 날짜 테이블을 만든다.
- CALCULATE는 FILTER에 의해 만든 테이블에서 [Sum of Amount]를 계산한다.
Accumulated Amount =
CALCULATE (
[Sum of Amount],
FILTER (
ALL ( 'Calendar'[Date] ),
'Calendar'[Date] <= MAX ( SHOP[O_Date] )
)
)
측정값 [Accumulated Amount]을 시각적 개체 테이블과, 영역형 차트를 통해 표현하면 아래와 같습니다.
2023년 2월부터 2024년 6월까지 Amount가 발생한 달에서 증가하고 있어, 누적값을 제대로 계산하고 있습니다.
이는 MAX 함수 입력으로 SHOP [O_Date]을 사용했기 때문입니다.
SHOP [O_Date]가 존재하지 않는 곳에서는 측정값 [Accumulated Amount]가 없기 때문에, 영역형 차트로 표시했을 때, 월간 간격이 일정하지 않습니다.
즉, 누적값을 표현하고 싶기 때문에, 테이블에 나타나지 않는 달은 그 이전 달과 같은 값을 유지하고 있어야 합니다.
시각화 X축에서 “데이터가 없는 항목 표시” 선택하면 아래처럼 보입니다. 더 이상한 그래프가 되었네요
누적값 DAX 식 - 캘린더 날짜 기준
모든 월에 누적값을 유지하기 위해서는 [Sum of Amount] 계산이 SHOP [O_Date] 기준이 아니라, 모든 날짜에 대해서 일어나야 합니다.
그래서 누적값 DAX 식에서 MAX 함수 입력으로 Calendar [Date]를 이용해야 합니다.
Accumulated Amount Calendar =
CALCULATE (
[Sum of Amount],
FILTER (
ALL ( 'Calendar'[Date] ),
'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
)
)
측정값 [Accumulated Amount Calendar]을 시각적 개체 테이블과, 영역형 차트를 통해 표현하면 아래와 같습니다.
MAX ('Calendar' [Date])는 필터 컨텍스트에서 값이 존재하기 때문에, FILTER 함수가 항상 테이블을 반환할 수 있고, 이에 따라 CALCULATE 함수가 계산할 수 있습니다.
이제 우리가 원하는 모습에 조금 더 가깝게 왔습니다.
그러나 이 또한 약간 문제가 있는데, SHOP [O_Date]의 마지막 날짜는 2024/06/15입니다. 그런데, Calendar [Date]는 12월까지 있기 때문에, 2024년 마지막까지 170으로 값이 나옵니다.
즉, SHOP [O_Date]의 마지막 날짜를 고려한 그래프를 만들려면 비교문을 사용해야 합니다.
마지막 DAX 식입니다.
변수 Last_Date를 통해 SHOP [O_Date]의 마지막 날짜를 찾았고, 이를 IF 문을 이용해 CALCULATE 계산 여부를 결정했습니다.
Accumulated Amount Calendar Lastdate =
VAR Last_Date =
CALCULATE (
LASTDATE ( SHOP[O_Date] ),
ALL ( SHOP )
)
RETURN
IF (
MIN ( 'Calendar'[Date] ) > Last_Date,
BLANK (),
CALCULATE (
[Sum of Amount],
FILTER (
ALL ( 'Calendar'[Date] ),
'Calendar'[Date] <= MAX ( 'Calendar'[Date] )
)
)
)
측정값 [Accumulated Amount Calendar LastDate]를 영역형 차트로 표현했습니다. 2024년 6월이 마지막입니다.
마치며
누적값 계산을 위한 여러 종류의 DAX 식을 소개했습니다.
분석할 데이터의 날짜 데이터의 성격에 따라, 아주 간단한 DAX 식으로도 충분할 수도 있고 아니면 좀 더 복잡한 식을 만들어야 할 수도 있습니다.
조금은 복잡하지만 최종적으로 측정값 [Accumulated Amount Calendar Lastdate]을 통해서 시작날짜부터 마지막날짜까지 누적값을 계산했습니다.
그래서 날짜 데이터를 다루는 DAX 식에 어떤 기본적인 방식이 포함되어 있는지 이해가 필요하고, 누적값은 좋은 예 중의 하나입니다. 잘 살펴보면 DAX 공부하는 데 도움이 많이 될 것 같네요.
참고로, Calendar [Date] 만을 활용했기 때문에, 이 경우 굳이 Calendar 테이블 만들 필요 없이 SHOP [O_Date]를 통해서도 같은 결과를 만들 수 있습니다. 다만 DAX 식에는 차이가 있습니다.
시간 인텔리전스 DAX 함수를 이용해 다른 형태의 누적값을 구하는 DAX 식은 다음 포스트에서 다루도록 하겠습니다.
Power BI DAX 기초 – 캘린더 이용 누적값 구하기
Power BI DAX 기초 – 캘린더 이용 누적값 구하기
목차 개요 Power BI 시간 인텔리전스 기능과 연속된 모든 날짜를 담고 있는 Calendar 테이블이 준비되어 있다면, 날짜 정보를 갖고 있는 데이터를 분석할 준비가 되었습니다. 아래
brightsomuch.tistory.com
'Power BI' 카테고리의 다른 글
| Power BI DAX 기초 – DATESINPERIOD 이동 평균값 구하기 (2) | 2024.07.23 |
|---|---|
| Power BI DAX 기초 – DATESYTD 연도별 누적값 구하기 (3) | 2024.07.22 |
| Power BI DAX 기초 - 캘린더 만들기 (0) | 2024.07.20 |
| Power BI 문자열 필터 기능 시각적 개체 Text Filter (0) | 2024.07.18 |
| Power BI DAX 기초 – 계산 열 키워드 찾기 SWITCH FILTER 활용 (0) | 2024.07.17 |
