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

Power Pivot의 날짜 테이블은 시간에 따른 데이터를 찾아보고 계산하는 데 필수적입니다. 이 문서에서는 날짜 테이블에 대해 자세히 알아보고 파워 피벗에서 날짜 테이블을 만드는 방법을 설명합니다. 이 문서에서는 특히 다음을 설명합니다.

• 날짜 및 시간별로 데이터를 찾고 계산하는 데 날짜 테이블이 중요한 이유
• Power Pivot을 사용하여 데이터 모델에 날짜 테이블을 추가하는 방법입니다.
• 날짜 테이블에 연도, 월, 기간과 같은 새 날짜 열을 만드는 방법입니다.
• 날짜 테이블과 팩트 테이블 간의 관계를 만드는 방법입니다.
• 시간과 함께 작업하는 방법.

이 문서는 파워 피벗을 처음 사용하는 사용자를 대상으로 합니다. 그러나 데이터 가져오기, 관계 만들기, 계산된 열 및 측정값 만들기에 대해 이미 잘 알고 있어야 합니다.

이 문서에서는 측정값 수식에서 DAX Time-Intelligence 함수를 사용하는 방법에 대해 설명 하지 않습니다 . DAX 시간 인텔리전스 함수를 사용하여 측정값을 만드는 방법에 대한 자세한 내용은 Excel의 파워 피벗의 시간 인텔리전스를 참조하세요.

내용

• 날짜 테이블 이해

• 데이터 모델에 날짜 테이블 추가

  • 관계형 데이터베이스에서 가져오기

  • Excel에서 날짜표 만들기

    • 방법: Excel에서 날짜 테이블을 만들고 데이터 모델에 복사

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

  • DAX 날짜 및 시간 함수

  • 일정 연도에 대한 수식 예제

    • 연도
    • 월
    • 분기
    • Month Name

  • 회계 연도에 대한 수식 예제

    • Fiscal Year
    • Fiscal Month
    • 회계 분기

• 공휴일 또는 특별한 날짜

• 사용자 지정 일정 - 13개의 4주 기간

  • 주
  • 마침표
  • 기간 회계 연도
  • 회계 연도의 기간

• 관계

  • 다중 관계
  • 비활성 관계
  • 여러 날짜 표

• 날짜 테이블 속성

• 시간 작업

• 날짜를 더 유용하게 만들기

• 부록

  • 텍스트 데이터 형식 날짜를 날짜 데이터 형식으로 변환
  • 추가 리소스

날짜 테이블 이해

거의 모든 데이터 분석에는 날짜 및 시간에 대한 데이터를 찾아보고 비교하는 작업이 포함됩니다. 예를 들어 지난 회계 분기의 매출액을 합산한 다음 다른 분기와 합계를 비교하거나 계정의 월말 결산 잔액을 계산할 수 있습니다. 이러한 각 경우에 특정 기간의 판매 트랜잭션 또는 잔액을 그룹화하고 집계하는 방법으로 날짜를 사용하고 있습니다.

파워 뷰 보고서

날짜 테이블에는 날짜와 시간의 다양한 표현이 포함될 수 있습니다. 예를 들어 날짜 테이블에는 회계 연도, 월, 분기 또는 기간과 같은 열이 있는 경우가 많으며, 피벗 테이블이나 파워 뷰 보고서에서 데이터를 분할하고 필터링할 때 필드 목록의 필드로 선택할 수 있습니다.

파워 뷰 필드 목록

연도, 월, 분기와 같은 날짜 열에 해당 범위 내의 모든 날짜가 포함되려면 날짜 테이블에 연속된 날짜 집합이 포함된 열이 하나 이상 있어야 합니다 . 즉, 해당 열에는 날짜 테이블에 포함된 각 연도의 매일 행이 하나씩 있어야 합니다.

예를 들어 찾아보려는 데이터의 날짜가 2010년 2월 1일에서 2012년 11월 30일까지이고 일정 연도로 보고하는 경우 최소 날짜 범위가 2010년 1월 1일에서 2012년 12월 31일까지인 날짜 테이블이 필요합니다. 날짜 테이블의 매 연도에는 각 연도의 모든 날짜가 포함되어야 합니다. 정기적으로 최신 데이터로 데이터를 새로 고치는 경우에는 종료 날짜를 1년 또는 2년 앞당겨 실행하여 시간이 지남에 따라 날짜 테이블을 업데이트할 필요가 없도록 할 수 있습니다.

연속된 날짜 집합이 있는 날짜 테이블

회계 연도에 대해 보고하는 경우 각 회계 연도에 대해 연속된 날짜 집합을 사용하여 날짜 테이블을 만들 수 있습니다. 예를 들어 회계 연도가 3월 1일에 시작하고 2010 회계연도부터 현재 날짜(예: 2013 회계연도)까지의 데이터가 있는 경우 2009년 3월 1일에 시작하여 최소 각 회계 연도의 매일 부터 2013 회계연도의 마지막 날짜까지 포함하는 날짜 테이블을 만들 수 있습니다.

달력 연도와 회계 연도 모두에 대해 보고하는 경우 별도의 날짜 테이블을 만들 필요가 없습니다. 단일 날짜 테이블에는 달력 연도, 회계 연도, 심지어 13개의 4주 일정에 대한 열이 포함될 수 있습니다. 중요한 것은 날짜 테이블에 포함된 모든 연도에 대한 연속된 날짜 집합이 포함되어 있다는 것입니다.

데이터 모델에 날짜 테이블 추가

데이터 모델에 날짜 테이블을 추가할 수 있는 몇 가지 방법이 있습니다.

• 관계형 데이터베이스 또는 기타 데이터 원본에서 가져옵니다.
• Excel에서 날짜 표를 만든 다음 Power Pivot에서 새 테이블에 복사하거나 연결합니다.
• Microsoft Azure Marketplace에서 가져옵니다.

이들 각각을 더 자세히 살펴보겠습니다.

관계형 데이터베이스에서 가져오기

데이터 웨어하우스나 다른 유형의 관계형 데이터베이스에서 데이터의 일부 또는 전부를 가져오는 경우 이미 날짜 테이블이 있고 이 테이블과 가져오려는 나머지 데이터 간의 관계가 있을 수 있습니다. 날짜 및 형식은 팩트 데이터의 날짜와 일치할 가능성이 높으며 날짜는 과거에 시작하여 훨씬 먼 미래로 이어질 수 있습니다. 가져오려는 날짜 테이블은 매우 크고 데이터 모델에 포함해야 하는 날짜 범위를 초과하는 날짜 범위를 포함할 수 있습니다. 파워 피벗의 표 가져오기 마법사의 고급 필터 기능을 사용하여 실제로 필요한 날짜와 특정 열만 선택적으로 선택할 수 있습니다. 이렇게 하면 통합 문서 크기를 크게 줄이고 성능을 향상시킬 수 있습니다.

테이블 가져오기 마법사

대부분의 경우 회계 연도, 주, 월 이름 등과 같은 추가 열은 가져온 테이블에 이미 존재하기 때문에 만들 필요가 없습니다. 그러나 데이터 모델로 날짜 테이블을 가져온 후 특정 보고 요구 사항에 따라 추가 날짜 열을 만들어야 하는 경우도 있습니다. 다행히 DAX를 사용하면 이 작업을 쉽게 수행할 수 있습니다. 나중에 날짜 테이블 필드를 만드는 방법에 대해 자세히 알아봅니다. 모든 환경은 다릅니다. 데이터 원본에 관련 날짜 또는 달력 테이블이 있는지 확실하지 않은 경우 데이터베이스 관리자에게 문의하세요.

Excel에서 날짜표 만들기

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

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

방법: Excel에서 날짜 테이블을 만들고 데이터 모델에 복사

1. Excel의 빈 워크시트의 A1 셀에 열 머리글 이름을 입력하여 날짜 범위를 식별합니다. 일반적으로 Date, DateTime 또는 DateKey와 같습니다.

2. 셀 A2에 시작 날짜를 입력합니다. 예를 들어 1/1/2010입니다.

3. 채우기 핸들을 클릭하고 종료 날짜가 포함된 행 번호로 아래로 끕니다. 예를 들어 12/31/2016입니다.
   

4. 날짜 열의 모든 행(A1 셀의 머리글 이름 포함)을 선택합니다.

5. 스타일 그룹에서 표로 서식을 클릭한 다음 스타일을 선택합니다.

6. 표 서식 대화 상자에서 확인을 클릭합니다.
   

7. 머리글을 포함하여 모든 행을 복사합니다.

8. Power Pivot의 홈 탭에서 붙여넣기를 클릭합니다.

9. 미리 보기 붙>여넣기에서테이블 이름에 날짜 또는 Calendar 등의 이름을 입력합니다. 첫 행을 열 머리글로 사용을선택한 다음 확인을 클릭합니다.
   
   Power Pivot의 새 날짜 테이블(이 예제에서는 Calendar)은 다음과 같습니다.
   

   참고

   데이터 모델에 추가를 사용하여 연결된 테이블을 만들 수도 있습니다. 그러나 이렇게 하면 통합 문서에 두 가지 버전의 날짜 테이블이 있기 때문에 통합 문서가 불필요하게 커집니다. 하나는 Excel에, 다른 하나는 Power Pivot에 있습니다..

이제 데이터 모델에 날짜 테이블이 있습니다. DAX를 사용하여 연도, 월 등의 새 날짜 열을 추가할 수 있습니다.

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

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

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

DAX 날짜 및 시간 함수

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

• DAX 날짜 및 시간 함수는 날짜/시간 데이터 형식을 사용합니다.
• 열의 값을 인수로 사용할 수 있습니다.
• 날짜 값을 반환 및/또는 조작하는 데 사용할 수 있습니다.

이러한 함수는 날짜 테이블에 사용자 지정 날짜 열을 만들 때 자주 사용되므로 이해하는 것이 중요합니다. 이러한 함수 중 여러 개를 사용하여 연도, 분기, 회계월 등의 열을 만듭니다.

DAX에는 다음과 같은 날짜 및 시간 함수가 포함되어 있습니다.

• 날짜
• DATEVALUE
• 다음날
• EDATE
• EOMONTH
• HOUR
• MINUTE
• MONTH
• NOW
• SECOND
• 시간
• TIMEVALUE
• 오늘
• WEEKDAY
• WEEKNUM
• YEAR
• YEARFRAC

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

일정 연도에 대한 수식 예제

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

년

=YEAR([date])

이 수식에서 YEAR 함수는 날짜 열의 값에서 연도를 반환합니다. Date 열의 값은 날짜/시간 데이터 형식이므로 YEAR 함수는 이 열에서 연도를 반환하는 방법을 알고 있습니다.

월

=MONTH([date])

이 수식에서는 YEAR 함수와 매우 유사하게 MONTH 함수를 사용하여 날짜 열에서 월 값을 반환할 수 있습니다.

분기

=INT(([Month]+2)/3)

이 수식에서는 INT 함수를 사용하여 날짜 값을 정수로 반환합니다. INT 함수에 대해 지정하는 인수는 월 열의 값으로, 2를 더한 다음 이를 3으로 나누어 분기 1에서 4를 구합니다.

Month Name

=FORMAT([date],"mmmm")

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

월 이름을 세 문자로 줄여서 반환하려면 format 인수에 "mmm"을 사용합니다.

요일

=FORMAT([date],"ddd")

이 수식에서는 FORMAT 함수를 사용하여 요일 이름을 가져옵니다. 요일만 축약해서 필요하므로, format 인수에 "ddd"를 지정합니다.

샘플 피벗 테이블

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

회계 연도에 대한 수식 예제

Fiscal Year

=IF([월]<= 6,[년],[년]+1)

이 예에서 회계 연도는 7월 1일에 시작됩니다.

회계 연도의 시작 및 종료 날짜가 달력 연도와 다른 경우가 많기 때문에 날짜 값에서 회계 연도를 추출할 수 있는 함수는 없습니다. 회계 연도를 받으려면 먼저 IF 함수를 사용하여 Month 값이 6보다 작거나 같은지 테스트합니다. 두 번째 인수에서 Month 값이 6보다 작거나 같으면 Year 열의 값을 반환합니다. 그렇지 않은 경우 연도의 값을 반환하고 1을 더합니다.

회계 연도 말 월 값을 지정하는 또 다른 방법은 단순히 월을 지정하는 측정값을 만드는 것입니다. 예를 들어 FYE:=6입니다. 그런 다음 월 번호 대신 측정값 이름을 참조할 수 있습니다. 예: =IF([월]<=[회수],[연도],[연도]+1). 이렇게 하면 여러 다른 수식으로 회계 연도 종료 월을 참조할 때 더 많은 유연성이 제공됩니다.

Fiscal Month

=IF([Month]<= 6, 6+[Month], [Month]- 6)

이 수식에서는 [월]의 값이 6보다 작거나 같은지 지정한 다음 6을 가져와 월의 값을 더하고 그렇지 않으면 [월]의 값에서 6을 뺍니다.

회계 분기

=INT(([FiscalMonth]+2)/3)

FiscalQuarter에 사용되는 수식은 해당 연도의 Quarter에 사용되는 수식과 거의 동일합니다. 유일한 차이점은 [Month] 대신 [FiscalMonth]를 지정한다는 것입니다.

공휴일 또는 특별한 날짜

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

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

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

날짜	공휴일
1/1/2010	새해
11/25/2010	추수감사절
12/25/2010	크리스마스
2011-01-01	새해
11/24/2011	추수감사절
12/25/2011	크리스마스
2012-01-01	새해
2012-11-22	추수감사절
12/25/2012	크리스마스
1/1/2013	새해
11/28/2013	추수감사절
12/25/2013	크리스마스
11/27/2014	추수감사절
12/25/2014	크리스마스
1/1/2014	새해
11/27/2014	추수감사절
12/25/2014	크리스마스
1/1/2015	새해
11/26/2014	추수감사절
12/25/2015	크리스마스
2016-01-01	새해
11/24/2016	추수감사절
12/25/2016	크리스마스

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

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

이 공식을 더 자세히 살펴보겠습니다.

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

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

그런 다음 검색하려는 날짜가 포함된 검색 열인 두 번째 인수를 지정합니다. 다음과 같이 휴일 테이블에 날짜 열을 지정합니다.

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

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

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

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

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

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

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

주

이 값은 회계 연도의 첫 번째 전체 주부터 시작하는 주 번호를 제공합니다. 이 예에서는 첫 번째 전체 주는 일요일에 시작하므로 Calendar 테이블의 첫 번째 회계 연도의 첫 번째 전체 주는 실제로 2010년 7월 4일에 시작하여 Calendar 테이블의 마지막 전체 주까지 계속됩니다. 이 값 자체는 분석에 그다지 유용하지 않지만 다른 28일 기간 수식에 사용하기 위해 계산해야 합니다.

=INT([date]-40356)/7)

이 공식을 더 자세히 살펴보겠습니다.

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

=INT([날짜])

그런 다음 첫 번째 회계 연도의 첫 번째 일요일을 찾으려고 합니다. 2010년 7월 4일인 것을 볼 수 있습니다.

이제 이 값에서 40356(이전 회계 연도의 마지막 일요일인 2010년 6월 27일의 정수)을 빼서 다음과 같이 Calendar 테이블의 날짜 시작 이후의 일 수를 구합니다.

=INT([날짜]-40356)

그런 다음 다음과 같이 결과를 7(일주일 중 일수)으로 나눕니다.

=INT(([date]-40356)/7)

결과는 다음과 같습니다.

마침표

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

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

이 공식을 더 자세히 살펴보겠습니다.

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

= INT([Week])

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

=INT([Week]+3)

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

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

결과는 다음과 같습니다.

기간 회계 연도

이 값은 일정 기간의 회계 연도를 반환합니다.

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

이 공식을 더 자세히 살펴보겠습니다.

먼저 Period의 값을 반환하고 12를 더하는 수식을 만듭니다.

=([마침표]+12)

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

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

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

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

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

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

결과는 다음과 같습니다.

회계 연도의 기간

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

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

이 공식은 조금 더 복잡하므로 먼저 우리가 더 잘 이해하는 언어로 설명하겠습니다. 이 수식은 [기간]의 값을 13으로 나누어 해당 연도의 기간 번호(1-13)를 구합니다. 이 숫자가 0이면 13을 반환합니다.

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

= MOD([Period],13)

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

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

결과는 다음과 같습니다.

샘플 피벗 테이블

아래 이미지는 VALUES의 Sales 팩트 테이블의 SalesAmount 필드와 ROWS의 Calendar 날짜 차원 테이블의 PeriodFiscalYear 및 PeriodInFiscalYear 필드가 있는 피벗 테이블을 보여 줍니다. SalesAmount는 컨텍스트에 대해 회계 연도 및 회계 연도의 28일 기간별로 집계됩니다.

관계

데이터 모델에서 날짜 테이블을 만든 후 피벗 테이블 및 보고서에서 데이터를 찾고 날짜 차원 테이블의 열을 기반으로 데이터를 집계하려면 트랜잭션 데이터가 포함된 팩트 테이블과 날짜 테이블 간에 관계를 만들어야 합니다.

날짜를 기반으로 관계를 만들어야 하므로 값이 날짜/시간(Date) 데이터 형식인 열 간에도 관계를 만들어야 합니다.

팩트 테이블의 모든 날짜 값에 대해 날짜 테이블의 관련 조회 열에 일치하는 값이 포함되어야 합니다. 예를 들어 DateKey 열의 값이 2012년 8월 15일 오전 12:00인 판매 팩트 테이블의 행(트랜잭션 레코드)은 날짜(Calendar) 테이블의 관련 날짜 열에 대응하는 값을 가져야 합니다. 이는 날짜 테이블의 날짜 열에 가능한 날짜가 포함된 연속된 날짜 범위를 포함해야 하는 가장 중요한 이유 중 하나입니다.

관계를 만들려고 할 때 유형 불일치 오류가 발생하면 사실 테이블의 열이 날짜 데이터 형식이 아니기 때문일 수 있습니다. 이 문제는 Power Pivot에서 날짜가 아닌 데이터 형식(일반적으로 텍스트 데이터 형식)을 날짜 데이터 형식으로 자동으로 변환할 수 없는 경우에 발생할 수 있습니다. 팩트 테이블에서 열을 계속 사용할 수 있지만 새 계산 열에서 DAX 수식으로 데이터를 변환해야 합니다. 부록 뒷부분의 텍스트 데이터 형식 날짜를 날짜 데이터 형식으로 변환을 참조하세요.

다중 관계

경우에 따라 여러 관계를 만들거나 여러 날짜 표를 만들어야 할 수 있습니다. 예를 들어 Sales 팩트 테이블에 DateKey, ShipDate, ReturnDate 등 여러 날짜 필드가 있는 경우 모두 Calendar 날짜 테이블의 날짜 필드와 관계를 가질 수 있지만 이 중 하나만 활성 관계가 될 수 있습니다. 이 경우 DateKey는 트랜잭션 날짜를 나타내므로 가장 중요한 날짜이므로 활성 관계로 가장 잘 사용됩니다. 나머지는 비활성 관계를 가지고 있습니다.

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

이 간단한 피벗 테이블은 DateKey의 거래 날짜 를 기준으로 총 판매량을 합산하려고 하기 때문에 올바르게 작동합니다. Total Sales 측정값은 DateKey의 날짜를 사용하고 Sales 테이블의 DateKey와 Calendar 날짜 테이블의 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 열 간의 관계를 사용하여 필터링합니다.

이제 피벗 테이블을 만들고 VALUES에 운송 날짜별 총 매출 측정값을 입력하고 행에 Fiscal Year 및 Fiscal Quarter를 입력하면 총합계는 동일하지만 회계 연도와 회계 분기에 대한 다른 모든 합계는 트랜잭션 날짜가 아닌 운송 날짜를 기반으로 하기 때문에 다릅니다.

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

여러 날짜 표

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

• 각 거래의 판매 날짜가 포함된 DateKey입니다.
• A ShipDate – 판매된 품목이 고객에게 배송된 날짜 및 시간
• ReturnDate – 반환된 하나 이상의 항목을 받은 날짜와 시간이 포함됩니다.

트랜잭션 날짜가 있는 DateKey 필드가 가장 중요합니다. 대부분의 집계는 이러한 날짜를 기준으로 수행되므로 대부분 Calendar 테이블의 날짜 열과 관계가 필요합니다. ShipDate 및 ReturnDate와 Calendar 테이블의 날짜 필드 사이에 비활성 관계를 만들어서 특수 측정값 수식을 필요로 하려면 운송 날짜 및 반환 날짜에 대한 추가 날짜 테이블을 만들 수 있습니다. 그러면 그들 사이에 적극적인 관계를 만들 수 있습니다.

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

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

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

날짜 테이블 속성

날짜 테이블 속성은 TOTALYTD, PREVIOUSMONTH, DATESBETWEEN 등의 Time-Intelligence 함수가 올바르게 작동하는 데 필요한 메타데이터를 설정합니다. 이러한 함수 중 하나를 사용하여 계산을 실행하면 Power Pivot의 수식 엔진은 필요한 날짜를 가져오기 위해 어디로 이동해야 하는지 알고 있습니다.

날짜 테이블 속성을 설정할 때 날짜 테이블과 날짜(날짜/시간) 데이터 형식의 날짜 열을 지정합니다.

방법: 날짜표 속성 설정

1. PowerPivot 창에서 Calendar 테이블을 선택합니다.
2. 디자인 탭에서 날짜 표로 표시를 클릭합니다.
3. 날짜 테이블로 표시 대화 상자에서 고유한 값과 날짜 데이터 형식이 있는 열을 선택합니다.

시간 작업

Excel 또는 SQL Server에서 날짜 데이터 형식이 있는 모든 날짜 값은 실제로 숫자입니다. 이 숫자에는 시간을 나타내는 숫자도 포함됩니다. 대부분의 경우 각 행의 시간은 자정입니다. 예를 들어 Sales 팩트 테이블의 DateTimeKey 필드에 10/19/2010 12:00:00 AM과 같은 값이 있는 경우 이는 값이 일 수준의 정밀도에 대한 것임을 의미합니다. DateTimeKey 필드 값에 시간이 포함된 경우(예: 2010/10/19 오전 8:44:00) 값이 최소 정밀도 수준임을 의미합니다. 값은 시간 수준 정밀도 또는 초 수준의 정밀도일 수도 있습니다. 시간 값의 정밀도는 날짜 테이블을 만드는 방법과 날짜 테이블과 팩트 테이블 간의 관계에 큰 영향을 미칩니다.

데이터를 일 정밀도 수준으로 집계할지 아니면 시간 정밀도 수준으로 집계할지 결정해야 합니다. 즉, 오전, 오후, 시간 등의 날짜 테이블 열을 피벗 테이블의 행, 열 또는 필터 영역의 시간 날짜 필드로 사용할 수 있습니다.

데이터를 시간 수준으로 집계하려면 날짜 테이블에 시간이 포함된 날짜 열이 필요합니다. 실제로, 날짜 범위의 모든 연도에 대해 매일 매시간 또는 매분마다 하나의 행이 있는 날짜 열이 필요합니다. 팩트 테이블의 DateTimeKey 열과 날짜 테이블의 날짜 열 간의 관계를 만들려면 일치하는 값이 있어야 하기 때문입니다. 상상할 수 있듯이 연도를 많이 포함하면 매우 큰 날짜 테이블이 될 수 있습니다.

그러나 대부분의 경우 사용자는 하루까지만 데이터를 집계하려고 합니다. 즉, 피벗 테이블의 행, 열 또는 필터 영역의 필드로 연도, 월, 주 또는 요일과 같은 열을 사용합니다. 이 경우 날짜 테이블의 날짜 열에는 앞에서 설명한 대로 일 년의 각 날짜에 대한 행이 하나만 포함되어야 합니다.

날짜 열에 시간 정밀도 수준이 포함되어 있지만 일 수준으로만 집계하려는 경우 팩트 테이블과 날짜 테이블 간의 관계를 만들기 위해 날짜 열의 값을 날짜 값으로 자르는 새 열을 만들어 팩트 테이블을 수정해야 할 수 있습니다. 즉, 10/19/2010 8:44:00AM 과 같은 값을 10/19/2010 12:00:00 AM으로 변환합니다. 그런 다음 날짜 테이블의 날짜 열과 값이 일치하므로 이 새 열과 날짜 열 간의 관계를 만들 수 있습니다.

예를 들어 보겠습니다. 이 이미지는 판매 팩트 테이블의 DateTimeKey 열을 보여줍니다. 이 테이블의 데이터에 대한 모든 집계는 연도, 월, 분기 등과 같은 Calendar 날짜 테이블의 열을 사용하여 일 수준으로만 수행하면 됩니다. 값에 포함된 시간은 관련이 없으며 실제 날짜만 해당됩니다.

이 데이터를 시간 수준으로 분석할 필요가 없기 때문에 Calendar 날짜 테이블의 날짜 열에 각 연도의 매일 매시간 및 매분에 대해 하나의 행을 포함할 필요가 없습니다. 따라서 날짜 테이블의 날짜 열은 다음과 같습니다.

Sales 테이블의 DateTimeKey 열과 Calendar 테이블의 Date 열 간 관계를 만들기 위해 Sales 팩트 테이블에 계산된 열을 새로 만들고 TRUNC 함수를 사용하여 DateTimeKey 열의 날짜 및 시간 값을 Calendar 테이블의 Date 열 값과 일치하는 날짜 값으로 잘라낼 수 있습니다. 수식은 다음과 같습니다.

=TRUNC([DateTimeKey],0)

이렇게 하면 DateTimeKey 열의 날짜가 포함된 새 열(DateKey라는 이름)과 각 행에 대해 오전 12:00:00 시간이 제공됩니다.

이제 이 새 (DateKey) 열과 Calendar 테이블의 날짜 열 사이의 관계를 만들 수 있습니다.

마찬가지로, DateTimeKey 열의 시간 정밀도를 시간 수준 정밀도로 줄이는 계산된 열을 Sales 테이블에 만들 수 있습니다. 이 경우 TRUNC 함수는 작동하지 않지만 다른 DAX 날짜 및 시간 함수를 사용하여 새 값을 추출하고 시간 수준의 정밀도로 다시 연결할 수 있습니다. 다음과 같은 수식을 사용할 수 있습니다.

= DATE (YEAR([DateTimeKey]), MONTH([DateTimeKey]), DAY([DateTimeKey]) ) + TIME (HOUR([DateTimeKey]), 0, 0)

새 열은 다음과 같습니다.

날짜 테이블의 날짜 열에 시간 수준의 정밀도 값이 있는 경우 둘 사이에 관계를 만들 수 있습니다.

날짜를 더 유용하게 만들기

날짜 테이블에 만드는 대부분의 날짜 열은 다른 필드에는 필요하지만, 실제로 분석에는 그다지 유용하지 않습니다. 예를 들어 이 문서 전체에서 참조하고 설명한 Sales 테이블의 DateKey 필드는 모든 트랜잭션에서 해당 트랜잭션이 특정 날짜 및 시간에 발생한 것으로 기록되기 때문에 중요합니다. 그러나 분석 및 보고의 관점에서 볼 때는 피벗 테이블이나 보고서의 행, 열 또는 필터 필드로 사용할 수 없기 때문에 그다지 유용하지 않습니다.

마찬가지로 이 예제에서 Calendar 테이블의 날짜 열은 실제로 매우 유용하고 중요하지만 피벗 테이블의 차원으로 사용할 수는 없습니다.

테이블과 테이블의 열을 최대한 유용하게 유지하고 피벗 테이블 또는 파워 뷰 보고서 필드 목록을 더 쉽게 탐색할 수 있도록 하려면 클라이언트 도구에서 불필요한 열을 숨기는 것이 중요합니다. 특정 테이블도 숨길 수 있습니다. 앞의 휴일 테이블에는 Calendar 테이블의 특정 열에 중요한 휴일이 포함되어 있지만 휴일 테이블의 날짜 및 휴일 열 자체를 피벗 테이블의 필드로 사용할 수는 없습니다. 여기서도 필드 목록을 더 쉽게 탐색할 수 있도록 전체 휴일 테이블을 숨길 수 있습니다.

날짜 작업의 또 다른 중요한 측면은 명명 규칙입니다. Power Pivot의 테이블과 열에 원하는 이름을 지정할 수 있습니다. 그러나 특히 통합 문서를 다른 사용자와 공유하는 경우 명명 규칙을 잘 지정하면 필드 목록뿐만 아니라 파워 피벗 및 DAX 수식에서도 테이블과 날짜를 쉽게 식별할 수 있습니다.

데이터 모델에 날짜 테이블이 있으면 데이터를 최대한 활용하는 데 도움이 되는 측정값을 만들 수 있습니다. 일부는 현재 연도의 총 판매액을 합산하는 것처럼 간단할 수도 있고, 특정 고유 날짜 범위로 필터링해야 하는 더 복잡할 수도 있습니다. Power Pivot 및 시간 인텔리전스 함수의 측정값에 대해 자세히 알아보세요.

부록

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

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

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

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

이 예제에서는 Sales라는 사실 테이블을 Power Pivot으로 가져왔습니다. 여기에는 DateTime이라는 열이 포함되어 있습니다. 값은 다음과 같이 표시됩니다.

서식 그룹 파워 피벗의 홈 탭에서 데이터 형식을 보면 텍스트 데이터 형식임을 알 수 있습니다.

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

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

이 문서의 앞부분에 있는 시간 작업 섹션에서 기억하세요. 분석이 시간 수준의 정밀도로 이루어져야 하는 경우가 아니라면 팩트 테이블의 날짜를 일 수준의 정밀도로 변환해야 합니다. 이를 염두에 두고 새 열의 값이 일 정밀도 수준(시간 제외)이 되기를 원합니다. 다음 수식을 사용하여 날짜/시간 열의 값을 날짜 데이터 형식으로 변환하고 시간 정밀도 수준을 제거할 수 있습니다.

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

이렇게 하면 새 열(이 경우 날짜)이 생깁니다. 파워 피벗은 날짜 값을 검색하고 데이터 형식을 자동으로 날짜로 설정합니다.

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

=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 리소스 센터