Excel Power Pivot의 날짜 테이블 이해 및 만들기

Excel에서 날짜 테이블 만들기

Excel에서 날짜 테이블을 만든 다음 데이터 모델의 새 테이블에 복사할 수 있습니다. 이것은 정말 매우 쉽게 할 수 있으며 많은 유연성을 제공합니다.

Excel에서 날짜 테이블을 만들 때는 연속된 날짜 범위의 단일 열로 시작합니다. 그런 다음 Excel 수식을 사용하여 Excel 워크시트에서 연도, 분기, 월, 회계 연도, 기간 등과 같은 추가 열을 만들거나 테이블을 데이터 모델에 복사한 후 계산 열로 만들 수 있습니다. Power Pivot에서 추가 날짜 열 만들기는 이 문서의 뒷부분에 있는 날짜 테이블에 새 날짜 열 추가 섹션에 설명되어 있습니다.

날짜 테이블에 새 날짜 열 추가

날짜 범위에서 모든 날짜를 정의하려면 각 연도에 대해 매일 하나의 행이 있는 단일 날짜 열이 있는 날짜 테이블이 중요합니다. 팩트 테이블과 날짜 테이블 간의 관계를 만드는 데도 필요합니다. 그러나 피벗 테이블 또는 파워 뷰 보고서의 날짜별로 분석할 때 매일 행이 한 개 있는 단일 날짜 열은 유용하지 않습니다. 날짜 테이블에 날짜 범위 또는 날짜 그룹에 대한 데이터를 집계하는 데 도움이 되는 열이 포함되도록 합니다. 예를 들어 월별 또는 분기별 판매액을 합산하거나 전년 대비 성장을 계산하는 측정값을 만들 수 있습니다. 이러한 각 경우에 날짜 테이블에는 해당 기간 동안 데이터를 집계할 수 있는 연도, 월 또는 분기 열이 필요합니다.

관계형 데이터 원본에서 날짜 테이블을 가져온 경우 원하는 다양한 형식의 날짜 열이 이미 포함될 수 있습니다. 경우에 따라 이러한 열 중 일부를 수정하거나 추가 날짜 열을 만들 수 있습니다. Excel에서 사용자 고유의 날짜 테이블을 만들고 데이터 모델에 복사하는 경우 특히 그렇습니다. 다행히 DAX의 날짜 및 시간 함수 를 사용하면 Power Pivot에서 새 날짜 열을 쉽게 만들 수 있습니다.

DAX 날짜 및 시간 함수

Excel 수식에서 날짜 및 시간 함수를 작업한 적이 있다면 날짜 및 시간 함수에 익숙할 것입니다. 이러한 함수는 Excel의 함수와 비슷하지만 몇 가지 중요한 차이점이 있습니다.

• DAX 날짜 및 시간 함수는 datetime 데이터 형식을 사용합니다.

• 열의 값을 인수로 사용할 수 있습니다.

• 날짜 값을 반환 및/또는 조작하는 데 사용할 수 있습니다.

이러한 함수는 날짜 테이블에서 사용자 지정 날짜 열을 만들 때 자주 사용되므로 이해해야 합니다. 이러한 함수를 여러 개 사용하여 Year, Quarter, FiscalMonth 등에 대한 열을 만듭니다.

DAX에는 다음 날짜 및 시간 함수가 포함됩니다.

• 날짜

• DATEVALUE

• 다음 날

• EDATE

• EOMONTH

• 시

• 분

• 달

• 지금

• 초

• 시간

• TIMEVALUE

• 오늘

• 평일

• WEEKNUM

• 년

• YEARFRAC

수식에 사용할 수 있는 다른 DAX 함수도 많이 있습니다. 예를 들어 여기에 설명된 많은 수식은 MOD 및 TRUNC와 같은 수학 및 삼각 함수, IF와 같은 논리 함수 및 FORMAT과 같은 텍스트 함수를 사용합니다. 다른 DAX 함수에 대한 자세한 내용은 이 문서의 뒷부분에 있는 추가 리소스 섹션을 참조하세요.

달력 연도에 대한 수식 예제

다음 예제에서는 Calendar이라는 날짜 테이블에 추가 열을 만드는 데 사용되는 수식을 설명합니다. Date라는 하나의 열은 이미 존재하며 2010년 1월 1일부터 2016년 12월 31일까지의 연속된 날짜 범위를 포함합니다.

Month Name

=FORMAT([date],"mmmm")

이 수식에서는 월 이름을 가져오기 위해 FORMAT 함수를 사용하여 숫자 값을 날짜 열에서 텍스트로 변환합니다. Date 열을 첫 번째 인수로 지정한 다음 형식으로 지정합니다. 월 이름에 모든 문자가 표시되기를 원하므로 "mmmm"을 사용합니다. 결과는 다음과 같습니다.

세 글자로 약어로 된 월 이름을 반환하려면 형식 인수에 "mmm"을 사용합니다.

요일

=FORMAT([date],"ddd")

이 수식에서는 FORMAT 함수를 사용하여 일 이름을 가져옵니다. 축약된 일 이름만 원하기 때문에 format 인수에 "ddd"를 지정합니다.

샘플 피벗 테이블

연도, 분기, 월 등과 같은 날짜에 대한 필드가 있으면 피벗 테이블 또는 보고서에서 사용할 수 있습니다. 예를 들어 다음 이미지는 VALUES의 Sales 팩트 테이블의 SalesAmount 필드와 ROWS의 일정 차원 테이블의 Year 및 Quarter를 보여 줍니다. SalesAmount는 연도 및 분기 컨텍스트에 대해 집계됩니다.

회계 연도에 대한 수식 예제

휴일 또는 특별 날짜

특정 날짜가 휴일 또는 다른 특별한 날짜임을 나타내는 날짜 열을 포함할 수 있습니다. 예를 들어 피벗 테이블에 휴일 필드를 슬라이서 또는 필터로 추가하여 새 연도 일의 판매 합계를 합산할 수 있습니다. 다른 경우에는 다른 날짜 열 또는 측정값에서 해당 날짜를 제외할 수 있습니다.

휴일이나 특별한 날을 포함하는 것은 매우 간단합니다. Excel에서 포함할 날짜가 포함된 테이블을 만들 수 있습니다. 그런 다음 데이터 모델에 추가를 복사하거나 사용하여 데이터 모델에 연결된 테이블로 추가할 수 있습니다. 대부분의 경우 테이블과 Calendar 테이블 간에 관계를 만들 필요는 없습니다. 참조하는 모든 수식은 LOOKUPVALUE 함수를 사용하여 값을 반환할 수 있습니다.

다음은 날짜 테이블에 추가할 휴일을 포함하는 Excel에서 만든 테이블의 예입니다.

날짜 테이블에서 Holiday 라는 열을 만들고 다음과 같은 수식을 사용합니다.

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

이 수식을 좀 더 신중하게 살펴보겠습니다.

LOOKUPVALUE 함수를 사용하여 Holidays 테이블의 휴일 열에서 값을 가져옵니다. 첫 번째 인수에서는 결과 값이 있는 열을 지정합니다. 휴일 테이블의 휴일 열은 반환하려는 값이므로 지정합니다.

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

그런 다음 검색할 날짜가 있는 검색 열인 두 번째 인수를 지정합니다. 휴일 테이블의 날짜 열은 다음과 같이 지정합니다.

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

마지막으로 휴일 테이블에서 검색할 날짜가 있는 Calendar 테이블의 열을 지정합니다. 물론 달력 테이블의 날짜 열입니다.

=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])

휴일 열은 휴일 테이블의 날짜와 일치하는 날짜 값이 있는 각 행의 휴일 이름을 반환합니다.

사용자 지정 달력 - 13개의 4주 기간

소매 또는 식품 서비스와 같은 일부 조직은 종종 13개의 4주 기간과 같은 다양한 기간에 대해 보고합니다. 13개의 4주 기간 달력을 사용하면 각 기간은 28일입니다. 따라서 각 기간에는 4개의 월요일, 4개의 화요일, 4개의 수요일 등이 포함됩니다. 각 기간에는 동일한 일 수가 포함되며 일반적으로 휴일은 매년 같은 기간에 속합니다. 요일마다 기간을 시작하도록 선택할 수 있습니다. 달력 또는 회계 연도의 날짜와 마찬가지로 DAX를 사용하여 사용자 지정 날짜가 있는 추가 열을 만들 수 있습니다.

아래 예제에서 첫 번째 전체 기간은 회계 연도의 첫 번째 일요일에 시작됩니다. 이 경우 회계 연도는 7/1에 시작됩니다.

마침표

이 사용자 지정 달력의 기간은 28일이며 항상 일요일에 시작됩니다. 이 열은 첫 번째 회계 연도의 첫 번째 일요일부터 시작하는 기간 수를 반환합니다.

=INT(([Week]+3)/4)

이 수식을 좀 더 신중하게 살펴보겠습니다.

먼저 다음과 같이 Week 열의 값을 정수로 반환하는 수식을 만듭니다.

=INT([Week])

그런 다음, 다음과 같이 해당 값에 3을 추가합니다.

=INT([Week]+3)

그런 다음 다음과 같이 결과를 4로 나눕니다.

=INT(([Week]+3)/4)

결과는 다음과 같습니다.

기간 회계 연도

이 값은 기간 동안 회계 연도를 반환합니다.

=INT(([Period]+12)/13)+2008

이 수식을 좀 더 신중하게 살펴보겠습니다.

먼저 Period에서 값을 반환하고 12를 추가하는 수식을 만듭니다.

= ([Period]+12)

회계 연도에는 13개의 28일 기간이 있으므로 결과를 13으로 나눕니다.

=(([Period]+12)/13)

테이블의 첫 해이므로 2010을 추가합니다.

=(([Period]+12)/13)+2010

마지막으로 INT 함수를 사용하여 결과의 일부를 제거하고 다음과 같이 13으로 나눈 경우 정수를 반환합니다.

=INT(([Period]+12)/13)+2010

결과는 다음과 같습니다.

FiscalYear의 기간

이 값은 각 회계 연도의 첫 번째 전체 기간(일요일부터 시작)부터 1 ~ 13의 기간 번호를 반환합니다.

=IF(MOD([Period],13), MOD([Period],13),13)

이 수식은 좀 더 복잡하므로 더 잘 이해할 수 있는 언어로 먼저 설명하겠습니다. 이 수식에서는 값을 [Period]에서 13으로 나누어 연도의 마침표 번호(1-13)를 가져옵니다. 이 숫자가 0이면 13을 반환합니다.

먼저 Period에서 13까지 값의 나머지를 반환하는 수식을 만듭니다. 다음과 같이 MOD (수학 및 삼각 함수)를 사용할 수 있습니다.

=MOD([Period],13)

이는 대부분의 경우 예제 달력 날짜 테이블의 처음 5일과 같이 해당 날짜가 첫 번째 회계 연도에 속하지 않기 때문에 Period 값이 0인 경우를 제외하고 원하는 결과를 제공합니다. IF 함수를 사용하여 이를 처리할 수 있습니다. 결과가 0이면 다음과 같이 13을 반환합니다.

=IF(MOD([Period],13),MOD([Period],13),13)

결과는 다음과 같습니다.

여러 관계

경우에 따라 여러 관계를 만들거나 여러 날짜 테이블을 만들어야 할 수 있습니다. 예를 들어 Sales 팩트 테이블에 DateKey, ShipDate 및 ReturnDate와 같은 여러 날짜 필드가 있는 경우 모두 달력 날짜 테이블의 날짜 필드에 대한 관계를 가질 수 있지만 그 중 하나만 활성 관계일 수 있습니다. 이 경우 DateKey는 트랜잭션 날짜를 나타내므로 가장 중요한 날짜이므로 활성 관계로 가장 적합합니다. 다른 항목에는 비활성 관계가 있습니다.

다음 피벗 테이블은 회계 연도 및 회계 분기별 총 매출을 계산합니다. Total Sales:=SUM([SalesAmount]) 수식이 포함된 Total Sales라는 측정값이 VALUES에 배치되고 달력 날짜 테이블의 FiscalYear 및 FiscalQuarter 필드가 ROWS에 배치됩니다.

이 직선 피벗 테이블은 DateKey의 트랜잭션날짜 까지 총 매출을 합산하려고 하므로 올바르게 작동합니다. Total Sales 측정값은 DateKey의 날짜를 사용하며 Sales 테이블의 DateKey와 일정 날짜 테이블의 Date 열 간에 관계가 있기 때문에 회계 연도 및 회계 분기로 합산됩니다.

비활성 관계

그러나 총 매출을 거래 날짜가 아니라 배송 날짜로 합산하려면 어떻게 해야 할까요? Sales 테이블의 ShipDate 열과 Calendar 테이블의 Date 열 간에 관계가 필요합니다. 해당 관계를 만들지 않으면 집계는 항상 트랜잭션 날짜를 기반으로 합니다. 그러나 하나만 활성화할 수 있지만 여러 관계를 가질 수 있으며 트랜잭션 날짜가 가장 중요하기 때문에 Calendar 테이블과의 활성 관계를 가져옵니다.

이 경우 ShipDate에는 비활성 관계가 있으므로 배송 날짜를 기준으로 데이터를 집계하기 위해 만든 측정값 수식은 USERELATIONSHIP 함수를 사용하여 비활성 관계를 지정해야 합니다.

예를 들어 Sales 테이블의 ShipDate 열과 Calendar 테이블의 Date 열 사이에 비활성 관계가 있으므로 배송 날짜별로 총 판매량을 합산하는 측정값을 만들 수 있습니다. 다음과 같은 수식을 사용하여 사용할 관계를 지정합니다.

배송 날짜별 총 판매액:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))

이 수식은 SalesAmount에 대한 합계를 계산하지만 Sales 테이블의 ShipDate 열과 Calendar 테이블의 Date 열 간의 관계를 사용하여 필터링합니다.

이제 피벗 테이블을 만들고 Ship Date 측정값으로 총 판매량을 VALUES에, ROWS에 회계 연도 및 회계 분기를 배치하면 총합계가 동일하지만 회계 연도 및 회계 분기의 다른 모든 합계 금액은 거래 날짜가 아닌 배송 날짜를 기준으로 하므로 다릅니다.

비활성 관계를 사용하면 하나의 날짜 테이블만 사용할 수 있지만 모든 측정값(예: 배송 날짜별 총 판매액)은 수식에서 비활성 관계를 참조해야 합니다. 다른 대안, 즉 여러 날짜 테이블을 사용합니다.

여러 날짜 테이블

팩트 테이블에서 여러 날짜 열을 사용하는 또 다른 방법은 여러 날짜 테이블을 만들고 서로 별도의 활성 관계를 만드는 것입니다. Sales 테이블 예제를 다시 살펴보겠습니다. 데이터를 집계할 날짜가 있는 세 개의 열이 있습니다.

• 각 트랜잭션에 대한 판매 날짜가 있는 DateKey입니다.

• ShipDate – 판매된 품목이 고객에게 배송된 날짜와 시간을 포함합니다.

• ReturnDate – 하나 이상의 항목이 반환된 날짜 및 시간이 포함된 입니다.

트랜잭션 날짜가 있는 DateKey 필드가 가장 중요합니다. 이러한 날짜에 따라 대부분의 집계를 수행하므로 일정 테이블의 날짜 열과 관계 관계를 가장 확실하게 원할 것입니다. ShipDate와 ReturnDate 간에 비활성 관계를 만들지 않으려는 경우 일정 테이블의 날짜 필드와 특수 측정값 수식이 필요하므로 배송 날짜 및 반환 날짜에 대한 추가 날짜 테이블을 만들 수 있습니다. 그런 다음, 이들 간에 활성 관계를 만들 수 있습니다.

이 예제에서는 ShipCalendar라는 다른 날짜 테이블을 만들었습니다. 물론 이는 추가 날짜 열을 만드는 것을 의미하며, 이러한 날짜 열은 다른 날짜 테이블에 있으므로 Calendar 테이블의 동일한 열과 구분하는 방식으로 이름을 지정하려고 합니다. 예를 들어 ShipYear, ShipMonth, ShipQuarter 등의 열을 만들었습니다.

피벗 테이블을 만들고 Total Sales 측정값을 VALUES에 배치하고 ROWS에 ShipFiscalYear 및 ShipFiscalQuarter를 배치하면 비활성 관계 및 특별 배송 날짜별 총 판매액 계산 필드를 만들 때와 동일한 결과가 표시됩니다.

이러한 각 방법에는 신중한 고려가 필요합니다. 단일 날짜 테이블과 여러 관계를 사용하는 경우 USERELATIONSHIP 함수를 사용하여 비활성 관계를 전송하는 특수 측정값을 만들어야 할 수 있습니다. 반면에 필드 목록에서 여러 날짜 테이블을 만드는 것은 혼동될 수 있으며 데이터 모델에 더 많은 테이블이 있으므로 더 많은 메모리가 필요합니다. 가장 적합한 항목을 실험해 볼 수 있습니다.

방법: Date Table 속성 설정

1. PowerPivot 창에서 일정 테이블을 선택합니다.

2. 디자인 탭에서 날짜 테이블로 표시를 클릭합니다.

3. 날짜 테이블로 표시 대화 상자에서 고유한 값과 날짜 데이터 형식이 있는 열을 선택합니다.

텍스트 데이터 형식 날짜를 날짜 데이터 형식으로 변환

경우에 따라 트랜잭션 데이터가 있는 팩트 테이블에 텍스트 데이터 형식의 날짜가 포함될 수 있습니다. 즉, 2012-12-04T11:47:09로 표시되는 날짜는 실제로 날짜가 아니거나 적어도 Power Pivot에서 이해할 수 있는 날짜 유형이 아닙니다. 그것은 정말 날짜처럼 읽는 텍스트입니다. 팩트 테이블의 날짜 열과 날짜 테이블의 날짜 열 간에 관계를 만들려면 두 열이 모두 Date 데이터 형식이어야 합니다.

일반적으로 텍스트 데이터 형식인 날짜 열의 데이터 형식을 날짜 데이터 형식으로 변경하려고 하면 Power Pivot에서 날짜를 해석하고 실제 날짜 데이터 형식으로 자동으로 변환할 수 있습니다. Power Pivot에서 데이터 형식 변환을 수행할 수 없는 경우 형식 불일치 오류가 발생합니다.

그러나 날짜를 실제 날짜 데이터 형식으로 변환할 수 있습니다. 새 계산 열을 만들고 DAX 수식을 사용하여 텍스트 문자열에서 연도, 월, 일, 시간 등을 구문 분석한 다음 Power Pivot에서 실제 날짜로 읽을 수 있는 방식으로 다시 연결할 수 있습니다.

이 예제에서는 Sales라는 팩트 테이블을 파워 피벗으로 가져왔습니다. DateTime이라는 열이 포함되어 있습니다. 값은 다음과 같이 표시됩니다.

서식 그룹 Power Pivot의 홈 탭에서 데이터 형식을 살펴보면 텍스트 데이터 형식임을 알 수 있습니다.

데이터 형식이 일치하지 않으므로 DateTime 열과 Date 테이블의 Date 열 간에 관계를 만들 수 없습니다. 데이터 형식을 날짜로 변경하려고 하면 형식 불일치 오류가 발생합니다.

이 경우 Power Pivot에서 데이터 형식을 텍스트에서 날짜로 변환할 수 없습니다. 이 열은 계속 사용할 수 있지만 실제 날짜 데이터 형식으로 전환하려면 텍스트를 구문 분석하고 Power Pivot에서 날짜 데이터 형식을 만들 수 있는 값으로 다시 만드는 새 열을 만들어야 합니다.

이 문서의 앞부분에 있는 시간 작업 섹션에서 기억하세요. 분석이 하루 중 시간 수준의 정밀도로 필요한 경우가 아니면 팩트 테이블의 날짜를 정밀도의 일 수준으로 변환해야 합니다. 이 점을 염두에 두고 새 열의 값이 시간(시간 제외)의 일 단위로 유지되도록 합니다. DateTime 열의 값을 날짜 데이터 형식으로 변환하고 다음 수식을 사용하여 전체 자릿수의 시간 수준을 제거할 수 있습니다.

=DATE(LEFT([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2))

이렇게 하면 새 열(이 경우 Date라는 이름)이 제공됩니다. Power Pivot은 날짜 값도 검색하고 데이터 형식을 자동으로 날짜로 설정합니다.

전체 자릿수의 시간 수준을 유지하려면 시간, 분 및 초를 포함하도록 수식을 확장하기만 하면 됩니다.

=DATE(LEFT([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2)) +

TIME(MID([DateTime],12,2), MID([DateTime],15,2), MID([DateTime],18,2))

이제 Date 데이터 형식의 Date 열이 있으므로 날짜와 날짜 열 간에 관계를 만들 수 있습니다.

추가 리소스

Power Pivot의 날짜

Power Pivot의 계산

퀵 스타트: 30분 내에 DAX 기본 익히기

데이터 분석 식 참조

DAX 리소스 센터