Power BI DAX 기초 – DATESINPERIOD 이동 평균값 구하기

목차

 

개요

 

캘린더 테이블을 이용해 날짜 정보를 갖는 테이블에서 누적값 구하는 DAX 식에 이전 포스트에서 다루었습니다. 조금씩 캘린더 테이블 및 시간 인텔리전스 DAX 함수에 익숙해지고 있는 것 같습니다. 

이번에는 평균값 및 이동 평균값에 대해 다뤄 보려고 합니다. 

평균값은 AVERAGE 함수로 간단히 구할 수 있습니다. 또한 Power BI 시간 인텔리전스 기능 또는 캘린더 테이블을 이용해서 날짜의 계층화 (일/월/분기/연 단위)를 하면 자동으로 평균값을 얻을 수 있죠. 

이동 평균값은 3개월, 6개월 등등 큰 흐름에서 평균의 흐름을 분석할 때 사용합니다. 이렇듯 구간을 설정할 수 있기 때문에, 흐름의 변화를 비교하면서 분석할 수 있는 장점이 있습니다.

 

이동 평균값 의미를 먼저 설명하고, 이를 어떻게 DAX 식으로 구현할 수 있는지 다루도록 하겠습니다.

 

즉, 아래와 같이 구간을 갖는 이동 평균값을 계산해서 그래프로 표현하는 것이 목적입니다.

 

 

아래 포스트에서 DAX 식으로 누적값을 계산할 수 있었습니다. 캘린더를 다루는 기본적인 내용을 담고 있기 때문에, 이동 평균값 계산 전에 꼭 참고하세요. 

• Power BI DAX 기초 – DATESYTD 연도별 누적값 구하기

Power BI DAX 기초 – DATESYTD 연도별 누적값 구하기

 

 

 

캘린더 테이블과 예제 데이터

 

보다 다양한 날짜 값을 얻기 위해서 아래처럼 SHOP [Order Date] 열을 만들었고, SHOP [Amount] 열과 함께 사용할 예정입니다.

 

즉, SHOP [Order Date]를 X 축으로 했을 때, SHOP [Amount]의 이동 평균값을 구하려고 합니다.

 

그리고 가장 기본적인 측정값 [Sum of Amount]를 아래와 같이 정의합니다.

 

Sum of Amount =
SUM ( SHOP[Amount] )

 

Calendar 테이블은 DAX 식으로 만들었습니다. 아래 포스트를 참고하세요.

 

• Power BI DAX 기초 - 캘린더 만들기

Power BI DAX 기초 - 캘린더 만들기

 

CALENDARAUTO 함수를 써서 Calendar [Date]는 2023년 1월 1일부터 2024년 12월 31일까지 날짜 정보를 담고 있습니다. 

 

그리고 Calendar [Date]와 SHOP [Order Date]를 아래와 같은 형태로 연결했습니다.

 

 

 

Power BI 시간 인텔리전스 기능으로 충분한 경우, 굳이 Calendar 테이블과 연결할 필요는 없습니다.

 

 

평균값 구하기

 

평균은 아래와 같이 정의할 수 있습니다.

• 평균 = 합 / 개수

이를 DAX 식으로 표현하는 것은 간단합니다. 합을 구하는 SUM 함수, 개수를 구하는 COUNT 함수, 두 측정값을 나누는 DIVIDE 함수를 이용하면 됩니다.

 

그래서 평균값을 계산하는 측정값 [Average DIVIDE]를 아래와 같이 정의할 수 있습니다.

 

Sum of Amount =
SUM ( SHOP[Amount] )

Count of Amount =
COUNT ( SHOP[Amount] )

Average DIVIDE =
DIVIDE (
    [Sum of Amount],
    [Count of Amount]
)

 

 

DAX AVERAGE 함수

 

위에서 SUM, COUNT, DIVIDE 함수를 사용했는데, DAX 함수 중에 AVERAGE가 있습니다.

 

• AVERAGE 함수(DAX) - DAX | Microsoft Learn

SUM, COUNT, AVERAGE 모두 집계 함수 범주에 포함되기에 사용 방법은 똑같습니다. 구문도 열 정보를 넣으면 필터 컨텍스트에 따라 평균을 구할 수 있습니다.

• AVERAGE(<column>)

측정값 [Average of Amount]는 아래처럼 아주 간단히 만들 수 있습니다.

 

Average of Amount =
AVERAGE ( SHOP[Amount] )

 

당연히 평균을 계산하는 측정값 [Average of Amount], [Average DIVIDE] 모두 같은 결과를 보여줍니다.

 

테이블 Year/Month는 Calendar 테이블에서 가져온 값입니다. 테이블이 연결되어 있기 때문에 Calendar 테이블이 SHOP 테이블에 필터가 전파됩니다.

 

그리고 아래를 보면 모든 월 정보가 있지 않다는 것을 알 수 있습니다. 이는 SHOP [Order Date]가 2023년 11월, 2024년 4월은 데이터가 없기 때문에 평균값 계산을 할 수 없기 때문입니다.

 

 

반응형

 

 

이동 평균값 이해

 

그림과 예제를 통해서 이동 평균값이 어떻게 계산되는지 설명을 해볼게요.

아래 두 방식은 서로 다른 DAX 식으로 구현됩니다. 따라서 DAX 식을 정확히 쓸 수 있도록 이해가 필요합니다.

 

 

이동 평균값 계산이 입력값 날짜 단위와 같을 때

 

예로 입력값이 일/월 단위로 존재할 때, 평균을 계산할 구간(윈도우 크기)이 이틀/두달, 그리고 이동 평균값을 매일/매달 계산하는 경우입니다.

 

아래 그림으로 표현했습니다.

 

 

 

그림을 설명하면, 평균값을 계산할 구간은 계산하는 날을 끝으로 이전 이틀/두달에 해당되고, 이 구간에서의 평균값을 계산합니다.

 

주의해야 할 점은 구간의 크기가 작은 경우 그림에서처럼 이동 평균값이 존재하지 않는 곳이 존재한다는 것입니다. 즉, 그래프로 그릴 때 비어있는 일/월을 어떻게 표현할지 고민해야 합니다. 

 

x-축에 Date 정보가 있다면, 일/월 구간은 일정하게 유지해야 합니다. 그리고 이동 평균값이 없는 곳을 그래프에서 표현을 하지 않거나, 존재하는 양쪽 값을 이어서 추정값으로 표현할 수 있습니다.

 

 

이동 평균값 계산이 입력값 날짜 단위와 같지 않을 때

 

이동 평균값 계산의 일반화된 상황이고 Power BI의 필터 컨텍스트에 따라 실제적으로 동작하는 방식입니다.

예로 입력값이 일 단위로 존재할 때, 평균을 계산할 구간(윈도우 크기)이 두달, 그리고 이동 평균값을 매달 계산하는 경우입니다.

 

 

 

평균값을 계산할 구간은 계산하는 날을 끝으로 해서 두달에 해당되고, 이 구간에서의 평균값을 계산합니다. 이동 평균값은 계산하는 시점에서 윈도우를 만들기 때문에, 그 구간에 포함된 입력값을 계산하면 됩니다. 

 

마찬가지로 공통적으로 주의해야 할 점은 윈도우 크기가 작은 경우 그림에서처럼 이동 평균값이 존재하지 않는 곳이 존재한다는 것입니다. 

 

 

이동 평균값 - AVERAGE DATESINPERIOD

 

앞 부분에서 측정값 [Average of Amount] 평균값 결과를 보면 해당 월/분기의 평균이라는 것을 알 수 있습니다.

 

즉, 이는 시각적 개체 테이블의 필터 컨텍스트가 특정 구간의 날짜 테이블을 반환하는 FILTER 함수와 동일한 역할을 수행하고 있다는 것을 의미합니다.

 

따라서 우리가 원하는 구간에서 평균값을 구하기 위해서, FILTER 함수의 입력 파라미터를 조정해서 이를 통해 그 구간의 날짜를 반환하는 테이블을 만들 수 있다면 이동 평균값을 구할 수 있는 거죠. 

 

그런데, 우리는 연도별 누적값을 구하는 과정을 통해 DATESYTD 함수가 편리하다는 것을 알았듯이, 시간 인텔리전스 DAX 함수 중에 날짜 구간을 쉽게 만들어주는 함수가 있는지 찾아봐야 합니다. 

 

• DAX(시간 인텔리전스 함수) - DAX | Microsoft Learn

그리고 여기에 유용한 DATESINPERIOD DAX 함수가 있습니다. 

 

DATESINPERIOD 함수는 지정된 시작 날짜로 시작하고 지정된 날짜 간격의 수와 형식에 대해 계속되는 날짜 열이 포함된 테이블을 반환합니다.

 

• DATESINPERIOD 함수(DAX) – DAX | Microsoft Learn

구문은 아래와 같습니다.

• DATESINPERIOD(<dates>, <start_date>, <number_of_intervals>, <interval>)

용어	정의
날짜	날짜 열입니다.
start_date	날짜 식입니다.
number_of_intervals	날짜를 추가하거나 뺄 간격 수를 지정하는 정수입니다.
interval	날짜를 이동할 간격입니다. 간격 값은 다음 DAY, MONTH, QUARTER, YEAR 중 하나일 수 있습니다.

 

---

 

실제로 어떻게 사용될 수 있는지 두 달 이동 평균값을 계산하는 DAX 식을 아래와 같이 작성할 수 있습니다.

 

Moving Average 2 MONTH =
CALCULATE (
    [Average of Amount],
    DATESINPERIOD (
        'Calendar'[Date],
        MAX ( 'Calendar'[Date] ),
        -2,
        MONTH
    )
)

 

DATESINPERIOD 함수의 입력 파라미터는 아래와 같이 이해할 수 있습니다.

 

MAX 함수가 사용된 이유는 현재 컨텍스트에서 마지막 날짜를 평균을 계산할 구간의 끝으로 설정하기 위함입니다.

 

다시 설명하면, MAX를 쓴다는 것은 "이동 평균값 계산이 입력값 날짜 단위와 같지 않을 때"를 반영합니다. 실제 SHOP [Order Date] 날짜를 기준으로 두 달전이 아니라, 필터 컨테스트 입장에서 두 달이 됩니다.

 

 

 

추가로 네 달 이동 평균값을 계산하는 DAX 식을 하나 더 만들어서 결과를 비교해 보려고 합니다.

 

Moving Average 4 MONTH =
CALCULATE (
    [Average of Amount],
    DATESINPERIOD (
        'Calendar'[Date],
        MAX ( 'Calendar'[Date] ),
        -4,
        MONTH
    )
)

---

 

연/월 단위를 갖는 테이블로 그 결과를 확인할 수 있습니다. 매달 마지막 날부터 두 달 또는 네 달을 평균값 구간으로 했을 때 이동 평균값입니다.

 

 

 

테이블은 숫자만 나와있기 때문에, 보다 효과적으로 결과를 확인하기 위해 시각적 개체 꺾은선형 및 누적 세로 막대형 차트를 사용했습니다.

 

평균값이 존재하지 않는 달이 있지만, 2달, 4달 윈도우를 갖고 있기 때문에, 이동 평균값은 계속 계산되고 있다는 것을 알 수 있습니다.

 

마치며

 

날짜를 갖는 데이터의 가장 기본적인 분석 (누적값, 이동 평균값)을 여러 포스트를 통해 다루었습니다.

 

이 과정에서 캘린더 테이블이 사용되었고, 시간 인텔리전스 DAX 함수도 일부 익힐 수 있었고, 시각적 개체를 통해 어떻게 표현될 수 있는지 알아봤습니다.

 

아주 기초적인 내용이었지만 중요한 개념을 다루고 있습니다. 향후 응용하시는데 도움이 되길 바랍니다.

 

DATESINPERIOD 함수식에 파라미터를 활용할 수 있습니다. 아래를 참고하세요.

 

Power BI DAX 기초 – 매개 변수 DAX 식에 활용 (구간 이동 평균값)

Power BI DAX 기초 – 매개 변수 DAX 식에 활용 (구간 이동 평균값)

 

Power BI DAX 기초 – 시간 인텔리전스 함수 내부 캘린더 DAX 식

Power BI DAX 기초 – 시간 인텔리전스 함수 내부 캘린더 DAX 식