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

날짜 테이블을 Excel

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

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

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

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

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

DAX 날짜 및 시간 함수

수식에서 날짜 및 시간 함수를 Excel 경우 날짜 및 시간 함수를 잘 알고 있을 수 있습니다. 이러한 함수는 해당 함수와 유사하지만 Excel 몇 가지 중요한 차이점이 있습니다.

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

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

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

이러한 함수는 날짜 테이블에서 사용자 지정 날짜 열을 만들 때 자주 사용되어 이해해야 합니다. 이러한 함수의 수를 사용하여 연도, 분기, FiscalMonth에 대한 열을 만들 것입니다.

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

• 날짜

• DATEVALUE

• NEXTDAY

• EDATE

• EOMONTH

• 시간

• 분

• 월

• 지금

• 두 번째

• 시간

• TIMEVALUE

• 오늘

• WEEKDAY

• WEEKNUM

• 연도

• YEARFRAC

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

일정 연도에 대한 수식 예제

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

월 이름

=FORMAT([date],"mmmm")

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

세 문자로 약어를 반환하려는 경우 형식 인수에서 "mmm"을 사용하게 됩니다.

요일

=FORMAT([date],"ddd")

이 수식에서는 FORMAT 함수를 사용하여 일 이름을 얻습니다. 약어 날짜 이름을 원하기 때문에 형식 인수에 "ddd"를 지정합니다.

샘플 피벗 테이블

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

회계 연도에 대한 수식 예제

공휴일 또는 특별한 날짜

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

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

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

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

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

이 수식을 좀 더 신중하게 살펴보자.

LOOKUPVALUE 함수를 사용하여 휴일 테이블의 휴일 열에서 값을 얻습니다. 첫 번째 인수에서 결과 값이 될 열을 지정합니다. 반환하려는 값이기 때문에 휴일 테이블에 휴일 열을 지정합니다.

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

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

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

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

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

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

사용자 지정 일정 - 134주 기간

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

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

마침표

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

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

이 수식을 좀 더 신중하게 살펴보자.

먼저 다음과 같이 주 열의 값을 정수로 반환하는 수식을 생성합니다.

=INT([Week])

그런 다음 이 값에 3을 추가합니다.

=INT([Week]+3)

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

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

결과는 다음과 같습니다.

기간 회계 연도

이 값은 기간에 대한 회계 연도를 반환합니다.

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

이 수식을 좀 더 신중하게 살펴보자.

먼저 기간에서 값을 반환하고 12를 추가하는 수식을 생성합니다.

= ([기간]+12)

회계 연도에 13일 28일 기간이 있기 때문에 결과를 13으로 분할합니다.

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

테이블의 첫 번째 연도이기 때문에 2010을 추가합니다.

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

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

=INT(([기간]+12)/13) + 2010

결과는 다음과 같습니다.

FiscalYear의 기간

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

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

이 수식은 좀 더 복잡하기 때문에 먼저 이해하기 좋은 언어로 설명할 것입니다. 이 수식은 값을 [기간]에서 13으로 나누고 연도에 기간 번호(1-13)를 얻습니다. 해당 숫자가 0이면 13을 반환합니다.

먼저 기간 13까지 값의 나머지를 반환하는 수식을 생성합니다. 이렇게 MOD(수학 및 삼각 함수)를 사용할 수 있습니다.

=MOD([Period],13)

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

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

결과는 다음과 같습니다.

여러 관계

경우에 따라 여러 관계를 만들거나 여러 날짜 테이블을 만들어야 할 수도 있습니다. 예를 들어 Sales 팩트 테이블에 DateKey, ShipDate 및 ReturnDate와 같은 날짜 필드가 여러 개 있는 경우 모두 일정 날짜 테이블의 날짜 필드에 관계가 있을 수 있지만 둘 중 하나만 활성 관계일 수 있습니다. 이 경우 DateKey는 트랜잭션 날짜를 나타내고 따라서 가장 중요한 날짜를 나타내기 때문에 활성 관계로 가장 잘 됩니다. 다른 사람에는 비활성 관계가 있습니다.

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

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

비활성 관계

하지만 거래 날짜가 아니라 배송 날짜로 총 매출을 합산하고 싶을 경우 어떻게 하나요? 판매 테이블의 ShipDate 열과 일정 테이블의 날짜 열 간의 관계가 필요합니다. 이러한 관계를 만들지 않는 경우 집계는 항상 트랜잭션 날짜를 기준으로 합니다. 그러나 하나만 활성 상태일 수 있으며 트랜잭션 날짜가 가장 중요하기 때문에 일정 테이블과 활성 관계를 맺는 경우에도 여러 관계를 맺을 수 있습니다.

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

예를 들어 판매 테이블의 ShipDate 열과 일정 테이블의 날짜 열 간에 비활성 관계가 있기 때문에 배송 날짜로 총 매출을 합산하는 측정값을 만들 수 있습니다. 이러한 수식을 사용하여 사용할 관계를 지정합니다.

배송 날짜:=계산(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))

이 수식은 단순히 SalesAmount에 대한 합계를 계산하지만 판매 테이블의 ShipDate 열과 일정 테이블의 날짜 열 간의 관계를 사용하여 필터링합니다.

이제 피벗 테이블을 만들고 배송 날짜별 총 판매량을 값으로 계산하고 행에 회계 연도 및 회계 분기를 기준으로 계산하면 총합계가 동일하지만 회계 연도 및 회계 분기의 다른 모든 합계 금액은 거래 날짜가 아니라 배송 날짜를 기반으로 하여 다르기 때문에 다릅니다.

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

여러 날짜 테이블

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

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

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

• ReturnDate – 반환된 하나 이상의 항목이 수신된 날짜와 시간이 있는 경우입니다.

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

이 예제에서는 ShipCalendar라는 다른 날짜 테이블을 만들어 졌습니다. 물론 이는 추가 날짜 열을 만드는 것을 의미하며, 이러한 날짜 열은 다른 날짜 테이블에 있기 때문에 일정 테이블의 동일한 열과 차별화하는 방식으로 이름을 지정합니다. 예를 들어 ShipYear, ShipMonth, ShipQuarter라는 열을 만들 수 있습니다.

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

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

방법: 날짜 테이블 속성 설정

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

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

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

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

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

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

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

이 예제에서는 Sales라는 팩트 테이블을 Power Pivot으로 가져왔다. DateTime이라는 열이 포함되어 있습니다. 값은 이처럼 표시됩니다.

서식 그룹 Power Pivot의 홈 탭의 데이터 형식을 살펴보면 텍스트 데이터 형식인 것으로 표시됩니다.

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

이 경우 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))

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

추가 리소스

Power Pivot의 날짜

Power Pivot의 계산

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

데이터 분석 식 참조

DAX 리소스 센터