Bảng ngày tháng trong Power Pivot là điều cần thiết để duyệt và tính toán dữ liệu theo thời gian. Bài viết này cung cấp hiểu biết kỹ về các bảng ngày tháng và cách bạn có thể tạo bảng trong Power Pivot. Đặc biệt, bài viết này mô tả:

  • Tại sao bảng ngày lại quan trọng trong việc duyệt và tính toán dữ liệu theo ngày và thời gian.

  • Cách sử dụng Power Pivot để thêm bảng dữ liệu vào Mô hình Dữ liệu.

  • Cách tạo cột ngày mới như Năm, Tháng và Thời gian trong bảng ngày.

  • Cách tạo mối quan hệ giữa bảng dữ kiện và bảng dữ kiện.

  • Cách làm việc với thời gian.

Bài viết này dành cho người dùng mới sử dụng Power Pivot. Tuy nhiên, điều quan trọng là cần phải hiểu rõ về việc nhập dữ liệu, tạo mối quan hệ, tạo các cột và số đo được tính toán.

Bài viết này không mô tả cách sử dụng hàm DAX Time-Intelligence trong công thức đo. Để biết thêm thông tin về cách tạo số đo với các hàm Time Intelligence của DAX, hãy xem Hẹn giờ Thông minh trong Power Pivot Excel.

Lưu ý: Trong Power Pivot, tên "đo" và "trường tính toán" đồng nghĩa. Chúng tôi đang sử dụng số đo tên trong toàn bộ bài viết này. Để biết thêm thông tin, hãy xem Giá trị đo trong Power Pivot.

Nội dung

Tìm hiểu về bảng ngày tháng

Hầu như tất cả phân tích dữ liệu đều liên quan đến việc duyệt và so sánh dữ liệu qua ngày và thời gian. Ví dụ, bạn có thể muốn tính tổng doanh thu cho quý tài chính quá khứ, sau đó so sánh các tổng này với các quý khác, hoặc bạn có thể muốn tính số dư đóng cửa cuối tháng cho một tài khoản. Trong từng trường hợp này, bạn đang dùng ngày tháng như một cách để nhóm và tổng hợp các giao dịch bán hàng hoặc số dư cho một khoảng thời gian cụ thể trong thời gian.

Báo cáo Power View

Tổng doanh thu tính theo quý tài chính trong Pivot Table

Bảng ngày tháng có thể chứa nhiều dạng biểu thị ngày và giờ khác nhau. Ví dụ, bảng ngày thường có các cột như Năm Tài chính, Tháng, Quý hoặc Giai đoạn mà bạn có thể chọn làm trường từ Danh sách Trường khi cắt và lọc dữ liệu của bạn trong PivotTable hoặc báo cáo Power View.

Danh sách Trường Power View

Danh sách Trường Power View

Đối với các cột ngày như Năm, Tháng và Quý để bao gồm tất cả các ngày trong phạm vi tương ứng, bảng ngày phải có ít nhất một cột chứa tập hợp các ngày liên tục. Nghĩa là, cột đó phải có một hàng cho mỗi ngày trong mỗi năm được bao gồm trong bảng ngày.

Ví dụ: nếu dữ liệu bạn muốn duyệt có các ngày từ ngày 1 tháng 2, 2010 đến ngày 30 tháng 11 năm 2012 và bạn báo cáo một năm theo lịch thì bạn sẽ muốn có bảng ngày có ít nhất một phạm vi ngày từ ngày 1 tháng 1 năm 2010 đến ngày 31 tháng 12 năm 2012. Mỗi năm trong bảng ngày của bạn phải chứa tất cả các ngày cho mỗi năm. Nếu bạn sẽ thường xuyên làm mới dữ liệu của mình với dữ liệu mới hơn, bạn có thể muốn chạy ngày kết thúc trước một hoặc hai năm, vì vậy bạn không cần phải cập nhật bảng ngày của mình khi thời gian trôi qua.

Bảng ngày tháng có tập hợp các ngày tháng liên tiếp nhau

Bảng ngày tháng có giá trị ngày tháng liên tiếp nhau

Nếu bạn báo cáo về năm tài chính, bạn có thể tạo bảng ngày tháng với một tập hợp các ngày liên tục cho mỗi năm tài chính. Ví dụ, nếu năm tài chính của bạn bắt đầu vào ngày 1 tháng 3 và bạn có dữ liệu cho năm tài chính 2010 đến ngày hiện tại (ví dụ: trong năm tài chính 2013), bạn có thể tạo một bảng ngày bắt đầu vào ngày 01/03/2009 và bao gồm ít nhất mỗi ngày trong mỗi năm tài chính đến ngày cuối cùng trong Năm Tài chính 2013.

Nếu bạn sẽ báo cáo về cả năm lịch và năm tài chính, bạn không cần tạo bảng ngày tháng riêng biệt. Một bảng ngày có thể bao gồm các cột cho năm lịch, năm tài chính và thậm chí là lịch của khoảng thời gian mười ba bốn tuần. Điều quan trọng là bảng ngày của bạn chứa một tập hợp các ngày liên tiếp cho tất cả các năm được bao gồm.

Thêm bảng ngày vào Mô hình Dữ liệu

Có một vài cách để thêm bảng ngày vào Mô hình Dữ liệu của bạn:

  • Nhập từ cơ sở dữ liệu quan hệ hoặc nguồn dữ liệu khác.

  • Tạo bảng ngày tháng trong Excel sau đó sao chép hoặc nối kết đến một bảng mới trong Power Pivot.

  • Nhập từ Microsoft Azure Marketplace.

Hãy xem kỹ hơn về mỗi ứng dụng trong số này.

Nhập từ cơ sở dữ liệu quan hệ

Nếu bạn nhập một số hoặc tất cả dữ liệu của mình từ nhà kho dữ liệu hoặc loại cơ sở dữ liệu có quan hệ khác, có khả năng đã có sẵn một bảng ngày và các mối quan hệ giữa nó và phần dữ liệu còn lại mà bạn đang nhập. Ngày và định dạng có thể khớp với ngày trong dữ liệu dữ kiện của bạn và ngày tháng có thể bắt đầu tốt trong quá khứ và đi xa trong tương lai. Bảng ngày bạn muốn nhập có thể rất lớn và chứa một phạm vi ngày nằm ngoài những gì bạn sẽ cần đưa vào Mô hình Dữ liệu của mình. Bạn có thể sử dụng các tính năng lọc nâng cao trong Trình hướng dẫn Nhập Bảng của Power Pivot để chỉ chọn có chọn ngày và cột cụ thể mà bạn thực sự cần. Điều này có thể làm giảm đáng kể kích cỡ sổ làm việc của bạn và cải thiện hiệu suất.

Trình hướng dẫn Nhập Bảng

Hộp thoại Trình hướng dẫn Nhập Bảng

Trong hầu hết các trường hợp, bạn sẽ không cần tạo bất kỳ cột bổ sung nào như Năm Tài chính, Tuần, Tên Tháng, v.v. vì chúng sẽ tồn tại trong bảng đã nhập. Tuy nhiên, trong một số trường hợp, sau khi bạn đã nhập bảng ngày vào Mô hình Dữ liệu, bạn có thể cần tạo thêm cột ngày, tùy thuộc vào một nhu cầu báo cáo cụ thể. May mắn là việc sử dụng DAX rất dễ dàng. Bạn sẽ tìm hiểu thêm về việc tạo trường bảng ngày sau này. Mỗi môi trường là khác nhau. Nếu bạn không chắc chắn liệu nguồn dữ liệu của bạn có ngày tháng hoặc bảng lịch có liên quan hay không, hãy trao đổi với người quản trị cơ sở dữ liệu của bạn.

Tạo bảng ngày tháng trong Excel

Bạn có thể tạo bảng ngày tháng trong Excel sau đó sao chép bảng đó vào bảng mới trong Mô hình Dữ liệu. Điều này thực sự khá dễ thực hiện và nó cung cấp cho bạn nhiều khả năng linh hoạt.

Khi bạn tạo bảng ngày tháng Excel, bạn bắt đầu với một cột duy nhất với phạm vi ngày tháng liên tiếp nhau. Sau đó, bạn có thể tạo các cột bổ sung như Năm, Quý, Tháng, Năm Tài chính, Giai đoạn, v.v. trong trang tính Excel bằng cách sử dụng công thức Excel hoặc sau khi sao chép bảng vào Mô hình Dữ liệu, bạn có thể tạo chúng dưới dạng cột được tính toán. Việc tạo cột ngày bổ sung trong Power Pivot được mô tả trong phần Thêm Cột Ngày Tháng Mới vào Bảng Ngày ở phần sau trong bài viết này.

Cách: Tạo bảng ngày tháng trong cơ Excel sao chép bảng đó vào Mô hình Dữ liệu

  1. Trong Excel tính trống, trong ô A1,nhập tên tiêu đề cột để xác định phạm vi ngày. Thông thường, hoạt động này sẽ có dạng như Date, DateTime hoặc DateKey.

  2. Trong ô A2,nhập ngày bắt đầu. Ví dụ: 01/01/2010.

  3. Bấm vào điều khiển điền và kéo nó xuống số hàng bao gồm ngày kết thúc. Ví dụ: 31/12/2016.

    Cột ngày tháng trong Excel

  4. Chọn tất cả hàng trong cột Ngày (bao gồm tên tiêu đề trong ô A1).

  5. Trong nhóm Kiểu, bấm vào Định dạng nhưBảng , rồi chọn một kiểu.

  6. Trong hộp thoại Định dạng như Bảng, bấm OK.

    Cột ngày tháng trong Power Pivot

  7. Sao chép tất cả các hàng, bao gồm cả tiêu đề.

  8. Trong Power Pivot, trên tab Trang đầu, bấm Dán.

  9. Trong Xem trước Dán > Tên Bảng, hãy nhập tên chẳng hạn như Ngày hoặc Lịch. Đánh dấu chọn Dùng hàng thứ nhất làm tiêu đề cột, sau đó bấm OK.

    Dán Dạng xem trước

    Bảng ngày mới (được đặt tên là Lịch trong ví dụ này) trong Power Pivot trông như sau:

    Bảng ngày tháng trong Power Pivot

    Lưu ý: Bạn cũng có thể tạo bảng được liên kết bằng cách sử dụng Thêm vào Mô hình Dữ liệu. Tuy nhiên, điều này làm cho sổ làm việc của bạn lớn một cách không cần thiết vì sổ làm việc có hai phiên bản của bảng ngày; một trong Excel và một trong Power Pivot.

Lưu ý: Ngày tên một từ khóa trong Power Pivot. Nếu bạn đặt tên cho bảng bạn tạo trong Ngày Power Pivot, bạn sẽ cần phải đặt tên bảng trong dấu ngoặc đơn trong mọi công thức DAX tham chiếu đến bảng đó trong một đối số. Tất cả các hình ảnh ví dụ và công thức trong bài viết này tham chiếu đến một bảng ngày được tạo trong Power Pivot có tên là Lịch.

Bây giờ bạn đã có một bảng ngày trong Mô hình Dữ liệu của bạn. Bạn có thể thêm các cột ngày mới, chẳng hạn như Năm, Tháng, v.v. bằng cách sử dụng DAX.

Thêm cột ngày mới vào bảng ngày

Bảng ngày tháng với cột ngày duy nhất có một hàng cho mỗi ngày cho mỗi năm là rất quan trọng để xác định tất cả các ngày trong một phạm vi ngày tháng. Bạn cũng cần tạo mối quan hệ giữa bảng dữ kiện và bảng ngày. Nhưng cột ngày tháng đơn lẻ với một hàng cho mỗi ngày không hữu ích khi phân tích theo ngày trong báo cáo PivotTable hoặc Power View. Bạn muốn bảng ngày bao gồm các cột giúp bạn tổng hợp dữ liệu cho một phạm vi hoặc nhóm ngày. Ví dụ, bạn có thể muốn tính tổng doanh thu theo tháng hay quý, hoặc bạn có thể tạo một số đo tính toán tăng trưởng theo năm. Trong từng trường hợp này, bảng ngày của bạn cần cột năm, tháng hoặc quý để cho phép bạn tổng hợp dữ liệu trong giai đoạn đó.

Nếu bạn đã nhập bảng ngày từ một nguồn dữ liệu có liên quan, bảng có thể đã chứa các kiểu cột ngày khác nhau mà bạn muốn. Trong một số trường hợp, bạn có thể muốn sửa đổi một số cột trong số đó hoặc tạo thêm các cột ngày. Điều này đặc biệt đúng nếu bạn tạo bảng ngày của riêng mình trong Excel và sao chép nó vào Mô hình Dữ liệu. May mắn là việc tạo cột ngày tháng mới trong Power Pivot khá dễ dàng với Hàm Ngày và Giờ trong DAX.

Mẹo: Nếu bạn chưa làm việc với DAX, một nơi tuyệt vời để bắt đầu tìm hiểu là với Hướng dẫn Nhanh: Tìm hiểu Kiến thức cơ bản về DAX trong 30 Phút trên Office.com.

Hàm Ngày và Giờ DAX

Nếu bạn đã từng làm việc với các hàm ngày và giờ trong công thức Excel ngày, có thể bạn sẽ quen thuộc với Hàm Ngày và Giờ. Mặc dù các hàm này tương tự như các hàm tương tự trong Excel, nhưng có một số khác biệt quan trọng:

  • Hàm Ngày và Giờ DAX sử dụng kiểu dữ liệu ngày giờ.

  • Chúng có thể lấy giá trị từ một cột làm đối số.

  • Chúng có thể được dùng để trả về và/hoặc điều khiển các giá trị ngày.

Các hàm này thường được sử dụng khi tạo các cột ngày tùy chỉnh trong bảng ngày tháng, vì vậy chúng rất quan trọng để hiểu. Chúng tôi sẽ sử dụng một số hàm này để tạo cột cho Năm, Quý, Tháng Tài chính, v.v..

Lưu ý: Hàm Ngày và Giờ trong DAX không giống như hàm 1003 thời gian thông minh. Tìm hiểu thêm về Nghiệp vụ Thông minh Thời gian trong Power Pivot Excel 2013.

DAX bao gồm các hàm Ngày và Giờ sau đây:

Có nhiều hàm DAX khác mà bạn cũng có thể dùng trong công thức của mình. Ví dụ: nhiều công thức được mô tả ở đây sử dụng Hàm lượng giác và Toán học như MODTRUNC,Hàm Lô-gic như IFvà Hàm Văn bản như FORMAT Để biết thêm thông tin về các hàm DAX khác, hãy xem mục Tài nguyên Bổ sung ở phần sau trong bài viết này.

Ví dụ công thức cho năm lịch

Các ví dụ sau đây mô tả công thức được sử dụng để tạo cột bổ sung trong bảng ngày có tên là Lịch. Một cột, được đặt tên là Ngày, đã tồn tại và chứa một phạm vi các ngày liên tiếp từ ngày 01/01/2010 đến 31/12/2016.

Năm

=YEAR([date])

Trong công thức này, hàm YEAR trả về năm từ giá trị trong cột Ngày. Vì giá trị trong cột Ngày thuộc kiểu dữ liệu datetime nên hàm YEAR biết cách trả về năm từ nó.

Cột Year

Tháng

=MONTH([date])

Trong công thức này, giống như với hàm YEAR, chúng ta chỉ cần sử dụng hàm MONTH để trả về giá trị tháng từ cột Ngày.

Cột Month

Quý

=INT(([Tháng]+2)/3)

Trong công thức này, chúng ta dùng hàm INT để trả về giá trị ngày làm số nguyên. Đối số mà chúng ta xác định cho hàm INT là giá trị từ cột Tháng, cộng 2 rồi chia cho 3 để nhận quý, 1 thông qua 4.

Cột Quarter

Tên Tháng

=FORMAT([date],"mmmm")

Trong công thức này, để lấy tên tháng, chúng ta sử dụng hàm FORMAT để chuyển đổi giá trị số từ cột Ngày thành văn bản. Chúng tôi chỉ định cột Ngày làm tham đối đầu tiên, sau đó là định dạng; chúng tôi muốn tên tháng của mình hiển thị tất cả các ký tự, vì vậy chúng tôi sử dụng "mmmm". Kết quả của chúng tôi sẽ có như sau:

Cột Month Name

Nếu chúng ta muốn trả về tên tháng được viết tắt thành ba chữ cái, chúng ta sẽ dùng "mmm" trong đối số format.

Ngày trong tuần

=FORMAT([date],"ddd")

Trong công thức này, chúng ta dùng hàm FORMAT để lấy tên ngày. Vì chúng ta chỉ muốn một tên ngày viết tắt, chúng tôi chỉ rõ "ddd" trong đối số format.

Cột Day of Week
PivotTable Mẫu

Sau khi bạn có các trường cho ngày, chẳng hạn như Năm, Quý, Tháng, v.v., bạn có thể sử dụng chúng trong PivotTable hoặc báo cáo. Ví dụ, ảnh sau đây cho thấy trường SalesAmount từ bảng dữ kiện Doanh số trong VALUES và Năm và Quý từ bảng chiều Lịch trong HÀNG. SalesAmount được tổng hợp cho ngữ cảnh năm và quý.

PivotTable Mẫu

Ví dụ công thức cho năm tài chính

Năm Tài chính

=IF([Tháng]Giá <= 6,[Năm],[Năm]+1)

Trong ví dụ này, năm tài chính bắt đầu vào ngày 1 tháng 7.

Không có hàm nào có thể trích xuất một năm tài chính từ một giá trị ngày vì ngày bắt đầu và kết thúc cho năm tài chính thường khác với ngày trong năm dương lịch. Để có năm tài chính, trước tiên chúng ta sử dụng hàm IF để kiểm tra xem giá trị cho Month có nhỏ hơn hoặc bằng 6 hay không. Trong đối số thứ hai, nếu giá trị cho Month nhỏ hơn hoặc bằng 6, thì trả về giá trị từ cột Năm. Nếu không, hãy trả về giá trị từ Năm và cộng thêm 1.

Cột Fiscal Year

Một cách khác để xác định giá trị tháng cuối năm tài chính là tạo một số đo chỉ xác định tháng. Ví dụ, FYE:=6. Sau đó bạn có thể tham chiếu tên đo lường thay cho số tháng. Ví dụ: =IF([Tháng]năm <=[Năm],[Năm],[Năm]+1). Điều này cho phép linh hoạt hơn khi tham chiếu tháng cuối năm tài chính theo một số công thức khácnhau.

Tháng Tài chính

=IF([Tháng]<= 6, 6+[Tháng], [Tháng]- 6)

Trong công thức này, chúng tôi chỉ định giá trị cho [Month] nhỏ hơn hoặc bằng 6, thì lấy 6 và cộng giá trị từ Month, nếu không thì lấy giá trị trừ đi 6 từ giá trị [Month].

Cột Fiscal Month

Quý Tài chính

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

Công thức chúng tôi sử dụng cho FiscalQuarter cũng tương tự như công thức Cho Quý trong năm dương lịch của chúng tôi. Sự khác biệt duy nhất là chúng tôi chỉ định [FiscalMonth] thay vì [Month].

Cột Fiscal Quarter

Ngày lễ hoặc ngày đặc biệt

Bạn có thể muốn bao gồm một cột ngày cho biết các ngày nhất định là ngày lễ hoặc một số ngày đặc biệt khác. Ví dụ: bạn có thể muốn tính tổng tổng doanh thu cho ngày Năm Mới bằng cách thêm trường Ngày lễ vào PivotTable, dưới dạng slicer hoặc bộ lọc. Trong các trường hợp khác, bạn có thể muốn loại trừ các ngày đó khỏi các cột ngày khác hoặc trong một số đo.

Bao gồm các ngày lễ hoặc ngày đặc biệt khá đơn giản. Bạn có thể tạo bảng trong Excel có ngày bạn muốn đưa vào. Sau đó, bạn có thể sao chép hoặc sử dụng Thêm vào Mô hình Dữ liệu để thêm nó vào Mô hình Dữ liệu dưới dạng bảng được liên kết. Trong hầu hết các trường hợp, bạn không cần tạo mối quan hệ giữa bảng và bảng Lịch. Mọi công thức tham chiếu đến công thức này đều có thể sử dụng hàm LOOKUPVALUE để trả về giá trị.

Dưới đây là ví dụ về bảng được tạo Excel gồm ngày lễ sẽ được thêm vào bảng ngày tháng:

Ngày

Ngày lễ

1/1/2010

Năm Mới

11/25/2010

Cảm ơn

12/25/2010

Giáng sinh

01/01/2011

Năm Mới

11/24/2011

Cảm ơn

12/25/2011

Giáng sinh

01/01/2012

Năm Mới

22/11/2012

Cảm ơn

12/25/2012

Giáng sinh

1/1/2013

Năm Mới

11/28/2013

Cảm ơn

12/25/2013

Giáng sinh

11/27/2014

Cảm ơn

12/25/2014

Giáng sinh

01/01/2014

Năm Mới

11/27/2014

Cảm ơn

12/25/2014

Giáng sinh

1/1/2015

Năm Mới

11/26/2014

Cảm ơn

12/25/2015

Giáng sinh

01/01/2016

Năm Mới

11/24/2016

Cảm ơn

12/25/2016

Giáng sinh

Trong bảng ngày, chúng ta tạo một cột có tên là Ngày lễ và dùng công thức như sau:

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

Hãy xem công thức này cẩn thận hơn.

Chúng tôi sử dụng hàm LOOKUPVALUE để nhận giá trị từ cột Ngày lễ trong bảng Holidays. Trong tham đối đầu tiên, chúng tôi xác định cột sẽ chứa giá trị kết quả. Chúng tôi chỉ định cột Holiday trong bảng Holidays vì đó là giá trị mà chúng tôi muốn trả về.

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

Sau đó, chúng tôi chỉ định tham đối thứ hai, cột tìm kiếm có ngày tháng mà chúng ta muốn tìm kiếm. Chúng tôi chỉ định cột Ngày trong bảng Holidays, như sau:

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

Cuối cùng, chúng tôi chỉ định cột trong bảng Lịch có ngày tháng mà chúng tôi muốn tìm kiếm trong bảng Holiday. Tất nhiên đây là cột Ngày trong bảng Lịch.

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

Cột Ngày lễ sẽ trả về tên ngày lễ cho mỗi hàng có giá trị ngày khớp với ngày trong bảng Holidays.

Bảng Holiday

Lịch tùy chỉnh - mười ba kỳ hạn bốn tuần

Một số tổ chức, chẳng hạn như dịch vụ bán lẻ hoặc thực phẩm, thường báo cáo về các giai đoạn khác nhau, như mười ba kỳ bốn tuần. Với lịch thời gian mười ba bốn tuần, mỗi khoảng thời gian là 28 ngày; do đó, mỗi giai đoạn chứa bốn Thứ hai, bốn Thứ ba, bốn Thứ tư, v.v. Mỗi giai đoạn chứa cùng một số ngày và thông thường, ngày lễ sẽ rơi vào trong cùng khoảng thời gian mỗi năm. Bạn có thể chọn bắt đầu một khoảng thời gian vào bất kỳ ngày nào trong tuần. Cũng giống như với ngày trong lịch hoặc năm tài chính, bạn có thể sử dụng DAX để tạo cột bổ sung với ngày tùy chỉnh.

Trong các ví dụ dưới đây, giai đoạn đầy đủ đầu tiên bắt đầu vào chủ nhật đầu tiên của năm tài chính. Trong trường hợp này, năm tài chính bắt đầu vào 01/07.

Tuần

Giá trị này cho chúng tôi số tuần bắt đầu từ tuần đầy đủ đầu tiên trong năm tài chính. Trong ví dụ này, tuần đầy đủ đầu tiên bắt đầu vào Chủ nhật, vì vậy tuần đầy đủ đầu tiên trong năm tài chính đầu tiên trong bảng Lịch thực sự bắt đầu vào ngày 04/07/2010 và tiếp tục qua tuần đầy đủ trước trong bảng Lịch. Mặc dù bản thân giá trị này không phải là tất cả hữu ích trong phân tích, nhưng cần tính toán để sử dụng trong các công thức khác trong kỳ 28 ngày.

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

Hãy xem công thức này cẩn thận hơn.

Trước tiên, chúng ta tạo một công thức trả về các giá trị từ cột Ngày dưới dạng số nguyên, như sau:

=INT([date])

Khi đó chúng ta sẽ muốn tìm chủ nhật đầu tiên trong năm tài chính đầu tiên. Chúng tôi thấy rằng đó là ngày 04/07/2010.

Cột Week

Bây giờ, lấy 40356 (vốn là số nguyên cho 27/06/2010, chủ nhật cuối cùng của năm tài chính trước đó) để lấy số ngày kể từ đầu ngày trong bảng Lịch của chúng tôi, như sau:

=INT([date]-40356)

Sau đó chia kết quả cho 7 (ngày trong một tuần), như sau:

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

Kết quả sẽ trông như sau:

Cột Week

Dấu chấm

Khoảng thời gian trong lịch tùy chỉnh này chứa 28 ngày và sẽ luôn bắt đầu vào ngày Chủ nhật. Cột này sẽ trả về số của khoảng thời gian bắt đầu bằng Chủ nhật đầu tiên trong năm tài chính đầu tiên.

=INT(([Tuần]+3)/4)

Hãy xem công thức này cẩn thận hơn.

Trước tiên, chúng ta tạo một công thức trả về giá trị từ cột Tuần dưới dạng số nguyên, như sau:

=INT([Tuần])

Sau đó thêm 3 vào giá trị đó, như thế này:

=INT([Tuần]+3)

Sau đó chia kết quả cho 4, như thế này:

=INT(([Tuần]+3)/4)

Kết quả sẽ trông như sau:

Cột Period

Năm Tài chính của Giai đoạn

Giá trị này trả về năm tài chính cho một khoảng thời gian.

=INT(([Dấu chấm]+12)/13)+2008

Hãy xem công thức này cẩn thận hơn.

Trước tiên, chúng ta tạo công thức trả về giá trị từ Kỳ và cộng 12:

= ([Dấu chấm]+12)

Chúng tôi chia kết quả cho 13, vì có mười ba giai đoạn 28 ngày trong năm tài chính:

=(([Dấu chấm]+12)/13)

Chúng tôi thêm 2010, vì đây là năm đầu tiên trong bảng:

=(([Dấu chấm]+12)/13)+2010

Cuối cùng, chúng ta dùng hàm INT để loại bỏ bất kỳ phần nào của kết quả và trả về một số nguyên, khi chia cho 13, như thế này:

=INT(([Dấu chấm]+12)/13)+2010

Kết quả sẽ trông như sau:

Cột năm tài chính của giai đoạn

Giai đoạn trong Năm Tài chính

Giá trị này trả về số của kỳ, 1 – 13, bắt đầu từ giai đoạn đầy đủ đầu tiên (bắt đầu từ chủ nhật) ở mỗi năm tài chính.

=IF(MOD([Khoảng thời gian],13), MOD([Kỳ hạn],13),13)

Công thức này phức tạp hơn một chút, vì vậy chúng tôi sẽ mô tả công thức trước bằng một ngôn ngữ mà chúng ta hiểu rõ hơn. Công thức này cho biết, hãy chia giá trị từ [Kỳ] cho 13 để có số kỳ hạn (1-13) trong năm. Nếu số đó là 0, thì trả về 13.

Trước tiên, chúng ta tạo một công thức trả về phần còn lại của giá trị từ Kỳ 13. Chúng ta có thể dùng HÀM MOD (Hàm lượng giác và Toán học) như sau:

=MOD([Giai đoạn],13)

Với hầu hết các phần, cung cấp cho chúng ta kết quả mà chúng ta muốn, ngoại trừ việc giá trị cho Giai đoạn là 0 vì những ngày đó không nằm trong năm tài chính đầu tiên, chẳng hạn như trong năm ngày đầu tiên trong bảng ngày trong lịch ví dụ của chúng tôi. Chúng ta có thể quan tâm đến vấn đề này bằng hàm IF. Trong trường hợp kết quả của chúng ta bằng 0, chúng ta trả về 13, như thế này:

=IF(MOD([Khoảng thời gian],13),MOD([Kỳ hạn],13),13)

Kết quả sẽ trông như sau:

Cột Period trong năm tài chính

PivotTable Mẫu

Hình dưới đây hiển thị một PivotTable với trường SalesAmount từ bảng dữ kiện Doanh số trong GIÁ TRỊ và trường PeriodFiscalYear và PeriodInFiscalYear từ bảng chiều ngày trong lịch trong HÀNG. SalesAmount được tổng hợp cho ngữ cảnh theo năm tài chính và khoảng thời gian 28 ngày trong năm tài chính.

PivotTable Mẫu cho năm tài chính

Quan hệ

Sau khi tạo bảng dữ liệu trong Mô hình Dữ liệu, để bắt đầu duyệt dữ liệu trong PivotTable và báo cáo, cũng như để tổng hợp dữ liệu dựa trên các cột trong bảng chiều ngày, bạn cần tạo mối quan hệ giữa bảng dữ kiện với dữ liệu giao dịch của mình và bảng ngày.

Vì bạn cần tạo mối quan hệ dựa trên ngày tháng, bạn sẽ muốn đảm bảo rằng bạn tạo mối quan hệ đó giữa các cột có giá trị thuộc kiểu dữ liệu ngày giờ (Ngày).

Đối với mỗi giá trị ngày trong bảng dữ kiện, cột tra cứu liên quan trong bảng ngày phải chứa các giá trị khớp nhau. Ví dụ, một hàng (bản ghi giao dịch) trong bảng dữ kiện Doanh số có giá trị là 15/08/2012 12:00 AM trong cột DateKey phải có giá trị tương ứng trong cột Ngày liên quan trong bảng ngày (có tên Lịch). Đây là một trong những lý do quan trọng nhất bạn muốn cột ngày của mình trong bảng ngày tháng chứa phạm vi ngày tháng liên tục bao gồm bất kỳ ngày nào có thể có trong bảng dữ kiện của bạn.

Quan hệ trong Dạng xem Sơ đồ

Lưu ý: Mặc dù cột ngày trong mỗi bảng phải có cùng kiểu dữ liệu (Ngày) nhưng định dạng của mỗi cột không quan trọng.

Lưu ý: Nếu Power Pivot không cho phép bạn tạo mối quan hệ giữa hai bảng, thì trường ngày có thể không lưu trữ ngày và thời gian ở cùng mức độ chính xác. Tùy theo định dạng cột, các giá trị có thể trông giống nhau nhưng được lưu trữ khác đi. Đọc thêm về làm việc với thời gian.

Lưu ý: Tránh sử dụng các phím thay thế số nguyên trong các mối quan hệ. Khi bạn nhập dữ liệu từ một nguồn dữ liệu có liên quan, cột ngày tháng và thời gian được thể hiện bằng khóa thay thế, đó là cột số nguyên được dùng để biểu thị một ngày duy nhất. Trong Power Pivot, bạn nên tránh tạo mối quan hệ bằng cách dùng phím ngày/giờ số nguyên và thay vào đó, hãy dùng các cột chứa các giá trị duy nhất có kiểu dữ liệu ngày. Mặc dù việc sử dụng khóa thay thế được coi là phương pháp tốt nhất trong các nhà kho dữ liệu truyền thống, nhưng khóa số nguyên không cần thiết trong Power Pivot và có thể khiến việc nhóm các giá trị trong PivotTable trở nên khó khăn theo các khoảng ngày khác nhau.

Nếu bạn gặp lỗi Nhập không khớp khi cố tạo mối quan hệ, thì có thể là do cột trong bảng dữ kiện không thuộc kiểu dữ liệu Ngày. Điều này có thể xảy ra khi Power Pivot không thể tự động chuyển đổi một dạng không phải là ngày (thường là một kiểu dữ liệu văn bản) thành một kiểu dữ liệu ngày. Bạn vẫn có thể dùng cột trong bảng dữ kiện, nhưng bạn sẽ phải chuyển đổi dữ liệu với công thức DAX trong một cột được tính mới. Xem mục Chuyển đổi ngày tháng trong kiểu dữ liệu văn bản thành kiểu dữ liệu ngày ở phần sau trong phụ lục.

Nhiều mối quan hệ

Trong một số trường hợp, có thể bạn cần tạo nhiều mối quan hệ hoặc tạo nhiều bảng ngày. Ví dụ: nếu có nhiều trường ngày trong bảng dữ kiện Doanh số, chẳng hạn như DateKey, ShipDate và ReturnDate, tất cả trường này có thể có mối quan hệ với trường Ngày trong bảng ngày trong Lịch nhưng chỉ một trong những trường này có thể là mối quan hệ hiện hoạt. Trong trường hợp này, vì DateKey đại diện cho ngày giao dịch nên do đó, ngày quan trọng nhất sẽ đóng vai trò là mối quan hệ hiện hoạt tốt nhất. Các mối quan hệ khác có mối quan hệ không hoạt động.

PivotTable sau tính tổng doanh thu theo Năm Tài chính và Quý Tài chính. Một số đo có tên là Tổng Doanh thu, với công thức Tổng Doanh thu:=SUM([SalesAmount]),được đặt trong trường VALUES, còn trường FiscalYear và FiscalQuarter từ bảng ngày trong Lịch được đặt trong HÀNG.

Tổng doanh thu theo quý tài chính PivotTable Danh sách Trường PivotTable

PivotTable chuyển tiếp thẳng này hoạt động chính xác vì chúng tôi muốn tính tổng doanh thu của mình theo ngàygiao dịch trong DateKey. Số đo Tổng Doanh thu của chúng tôi sử dụng ngày trong DateKey và được tính tổng theo năm tài chính và quý tài chính vì có mối quan hệ giữa DateKey trong bảng Doanh số và cột Ngày trong bảng ngày trong Lịch.

Mối quan hệ Không hoạt động

Nhưng nếu chúng tôi muốn tính tổng doanh thu của mình không phải theo ngày giao dịch, mà theo ngày giao hàng thì sao? Chúng ta cần có mối quan hệ giữa cột Ngày Vận chuyển trong bảng Doanh số và cột Ngày trong bảng Lịch. Nếu chúng ta không tạo mối quan hệ đó thì các kết tập của chúng ta luôn dựa trên ngày giao dịch. Tuy nhiên, chúng ta có thể có nhiều mối quan hệ, mặc dù chỉ một mối quan hệ có thể hiện hoạt và vì ngày giao dịch là quan trọng nhất nên nó có mối quan hệ hiện hoạt với bảng Lịch.

Trong trường hợp này, Ngày Vận chuyển có mối quan hệ không hoạt động, vì vậy bất kỳ công thức đo nào được tạo ra để tổng hợp dữ liệu dựa trên ngày vận chuyển đều phải xác định mối quan hệ không hoạt động bằng cách dùng hàm USERELATIONSHIP.

Ví dụ, vì có mối quan hệ không hoạt động giữa cột Ngày Vận chuyển trong bảng Doanh số và cột Ngày trong bảng Lịch, chúng ta có thể tạo một số đo tính tổng doanh thu theo ngày vận chuyển. Chúng ta dùng công thức như thế này để xác định mối quan hệ cần dùng:

Tổng Doanh thu theo Ngày Vận chuyển:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))

Công thức này chỉ nói: Tính tổng cho SalesAmount nhưng lọc bằng cách sử dụng mối quan hệ giữa cột Ngày Vận chuyển trong bảng Doanh số và cột Ngày trong bảng Lịch.

Bây giờ, nếu chúng ta tạo PivotTable và đặt số đo Tổng Doanh thu theo Ngày Vận chuyển trong VALUES và Năm Tài chính và Quý Tài chính trên CÁC HÀNG, chúng ta sẽ thấy cùng một Tổng Cuối nhưng tất cả các số tiền khác cho năm tài chính và quý tài chính khác nhau vì chúng dựa trên ngày vận chuyển chứ không phải ngày giao dịch.

Tổng doanh thu tính theo ngày vận chuyển trong PivotTable Danh sách Trường PivotTable

Việc sử dụng mối quan hệ không hoạt động cho phép bạn chỉ sử dụng một bảng ngày nhưng nó yêu cầu mọi số đo (như Tổng Doanh thu theo Ngày Vận chuyển), tham chiếu đến mối quan hệ không hoạt động trong công thức của nó. Có một giải pháp thay thế khác, đó là sử dụng nhiều bảng ngày.

Nhiều bảng ngày

Một cách khác để làm việc với nhiều cột ngày trong bảng dữ kiện là tạo nhiều bảng ngày và tạo các mối quan hệ hiện hoạt riêng biệt giữa chúng. Chúng ta hãy xem lại ví dụ bảng Doanh số của chúng tôi. Chúng ta có ba cột kèm theo ngày tháng mà chúng ta có thể muốn tổng hợp dữ liệu về:

  • DateKey kèm theo ngày bán cho từng giao dịch.

  • Ngày Vận chuyển – với ngày và thời gian giao mặt hàng đã bán cho khách hàng.

  • Ngày Trả lại – với ngày và giờ nhận được một hoặc nhiều mục.

Hãy nhớ rằng, trường DateKey với ngày giao dịch là quan trọng nhất. Chúng tôi sẽ thực hiện hầu hết các kết quả tổng hợp dựa trên những ngày này, vì vậy chắc chắn nhất là chúng tôi sẽ muốn có một mối quan hệ giữa nó và cột Ngày trong bảng Lịch. Nếu chúng ta không muốn tạo mối quan hệ không hoạt động giữa Ngày Vận chuyển và Ngày Vận chuyển và trường Ngày trong bảng Lịch, do đó cần có công thức đo lường đặc biệt, chúng ta có thể tạo thêm các bảng ngày cho ngày vận chuyển và ngày trả về. Sau đó, chúng ta có thể tạo các mối quan hệ hiện hoạt giữa chúng.

Quan hệ có nhiều bảng trong Dạng xem Sơ đồ

Trong ví dụ này, chúng tôi đã tạo một bảng ngày khác có tên là ShipCalendar. Tất nhiên điều này cũng có nghĩa là tạo các cột ngày bổ sung, và vì các cột ngày này sẽ ở trong một bảng ngày khác, chúng ta muốn đặt tên theo cách khác để chúng phân biệt chúng với cùng các cột trong bảng Lịch. Ví dụ, chúng tôi đã tạo các cột có tên là ShipYear, ShipMonth, ShipQuarter, v.v..

Nếu chúng ta tạo PivotTable và đặt số đo Tổng Doanh thu theo GIÁ TRỊ, và đối số ShipFiscalYear và ShipFiscalQuarter trên CÁC HÀNG, chúng ta sẽ thấy cùng những kết quả như khi tạo mối quan hệ không hoạt động và trường tính toán Tổng Doanh thu đặc biệt theo Ngày Vận chuyển.

Tổng doanh thu dựa trên ngày vận chuyển trong PivotTable với lịch vận chuyển Danh sách Trường Pivot Table

Mỗi phương pháp trong số này đòi hỏi phải cân nhắc cẩn thận. Khi sử dụng nhiều mối quan hệ với một bảng ngày, bạn có thể phải tạo các số đo đặc biệt có tác dụng chuyển các mối quan hệ không hoạt động bằng cách sử dụng hàm USERELATIONSHIP. Mặt khác, việc tạo nhiều bảng ngày có thể gây nhầm lẫn trong Danh sách Trường và vì bạn có nhiều bảng hơn trong Mô hình Dữ liệu nên sẽ cần nhiều bộ nhớ hơn. Thử nghiệm với những gì phù hợp nhất với bạn.

Thuộc tính Bảng Ngày

Thuộc tính Date Table đặt siêu dữ liệu cần Time-Intelligence để các hàm như TOTALYTD, PREVIOUSMONTH và DATESBETWEEN hoạt động chính xác. Khi một phép tính được chạy bằng cách dùng một trong các hàm này, công cụ công thức của Power Pivot biết nơi để lấy ngày tháng cần thiết.

Cảnh báo: Nếu thuộc tính này không được đặt, các số đo bằng cách sử dụng hàm DAX Time-Intelligence có thể trả về kết quả không chính xác.

Khi đặt thuộc tính Bảng Ngày, bạn chỉ định bảng ngày và cột ngày của kiểu dữ liệu Ngày (ngày giờ) trong đó.

Hộp thoại Đánh dấu Là Bảng Ngày tháng

Cách: Đặt thuộc tính Bảng Ngày

  1. Trong cửa PowerPivot, chọn bảng Lịch.

  2. Trên tab Thiết kế, bấm vào Đánh dấu là ngày Bảng.

  3. Trong hộp thoại Đánh dấu là Bảng Ngày, hãy chọn một cột có các giá trị duy nhất và kiểu dữ liệu Ngày.

Làm việc với thời gian

Tất cả các giá trị ngày có kiểu dữ liệu Ngày Excel hoặc SQL Server ra đều là một số. Được bao gồm trong số đó là các chữ số tham chiếu đến một thời gian. Trong nhiều trường hợp, thời gian cho mỗi hàng là nửa đêm. Ví dụ, nếu một trường DateTimeKey trong bảng dữ kiện Doanh số có các giá trị như 19/10/2010 12:00:00 AM, điều này có nghĩa là các giá trị ở mức độ chính xác ngày. Nếu các giá trị trường DateTimeKey có thời gian bao gồm, ví dụ: 19/10/2010 8:44:00 AM, điều này có nghĩa là các giá trị ở mức độ chính xác phút. Giá trị cũng có thể là độ chính xác của mức giờ hoặc thậm chí là mức chính xác giây. Mức độ chính xác trong giá trị thời gian sẽ có ảnh hưởng đáng kể đến cách bạn tạo bảng ngày và mối quan hệ giữa nó và bảng dữ kiện của bạn.

Bạn cần xác định xem bạn sẽ tổng hợp dữ liệu của mình đến mức chính xác ngày hay đến mức độ chính xác theo thời gian. Nói cách khác, bạn có thể muốn dùng các cột trong bảng ngày tháng như Sáng, Chiều hoặc Giờ làm trường ngày tháng trong khu vực Hàng, Cột hoặc Bộ lọc của PivotTable.

Lưu ý: Ngày là đơn vị thời gian nhỏ nhất mà các hàm Time Intelligence của DAX có thể làm việc. Nếu bạn không cần làm việc với các giá trị thời gian, bạn nên giảm độ chính xác của dữ liệu để dùng ngày làm đơn vị tối thiểu.

Nếu bạn định tổng hợp dữ liệu của mình theo mức thời gian thì bảng ngày của bạn sẽ cần một cột ngày có bao gồm thời gian. Thực tế, nó sẽ cần một cột ngày với một hàng cho mỗi giờ, hoặc có thể thậm chí là mỗi phút mỗi ngày, cho mỗi năm trong phạm vi ngày. Lý do là vì để tạo mối quan hệ giữa cột DateTimeKey trong bảng dữ kiện và cột ngày trong bảng ngày, bạn phải có các giá trị khớp nhau. Như bạn có thể tưởng tượng, nếu bạn bao gồm nhiều năm, điều này có thể tạo ra một bảng ngày rất lớn.

Tuy nhiên, trong hầu hết các trường hợp, bạn chỉ muốn tổng hợp dữ liệu trong ngày. Nói cách khác, bạn sẽ sử dụng các cột như Năm, Tháng, Tuần hoặc Ngày trong Tuần làm trường trong khu vực Hàng, Cột hoặc Bộ lọc của PivotTable. Trong trường hợp này, cột ngày tháng trong bảng ngày chỉ cần chứa một hàng cho mỗi ngày trong một năm, như chúng tôi đã mô tả ở phần trước.

Nếu cột ngày của bạn bao gồm mức độ chính xác theo thời gian nhưng bạn sẽ chỉ tổng hợp đến một mức ngày, để tạo mối quan hệ giữa bảng dữ kiện và bảng ngày, bạn có thể phải sửa đổi bảng dữ kiện của mình bằng cách tạo một cột mới để cắt cụt các giá trị trong cột ngày thành một giá trị ngày. Nói cách khác, chuyển đổi một giá trị như 19/10/2010 8:44:00AM thành 19/10/2010 12:00:00 AM. Sau đó, bạn có thể tạo mối quan hệ giữa cột mới này và cột ngày trong bảng ngày vì các giá trị khớp nhau.

Chúng ta hãy xem ví dụ. Hình ảnh này hiển thị cột DateTimeKey trong bảng dữ kiện Doanh số. Tất cả các kết tập cho dữ liệu trong bảng này chỉ cần ở mức ngày, bằng cách sử dụng các cột trong bảng ngày trong Lịch như Năm, Tháng, Quý, v.v.. Thời gian được đưa vào giá trị không liên quan, chỉ có ngày thực tế.

Cột DateTimeKey

Vì chúng ta không cần phân tích dữ liệu này theo mức thời gian nên chúng ta không cần cột Ngày trong bảng Ngày trong Lịch để đưa vào một hàng cho mỗi giờ và mỗi phút mỗi ngày trong mỗi năm. Vì vậy, cột Ngày trong bảng ngày của chúng tôi sẽ trông như thế này:

Cột ngày tháng trong Power Pivot

Để tạo mối quan hệ giữa cột DateTimeKey trong bảng Doanh số và cột Ngày trong bảng Lịch, chúng ta có thể tạo cột được tính mới trong bảng dữ kiện Doanh số, đồng thời sử dụng hàm TRUNC để cắt giá trị ngày và thời gian trong cột DateTimeKey thành giá trị ngày khớp với giá trị trong cột Ngày trong bảng Lịch. Công thức của chúng tôi trông như thế này:

=TRUNC([DateTimeKey],0)

Tùy chọn này cung cấp cho chúng tôi một cột mới (chúng tôi đặt tên là DateKey) với ngày từ cột DateTimeKey và thời gian là 12:00:00 SÁNG cho mỗi hàng:

Cột DateKey

Bây giờ chúng ta có thể tạo mối quan hệ giữa cột (DateKey) mới này và cột Ngày trong bảng Lịch.

Tương tự, chúng ta có thể tạo một cột được tính trong bảng Doanh số mà làm giảm độ chính xác thời gian trong cột DateTimeKey đến mức độ chính xác giờ. Trong trường hợp này, hàm TRUNC sẽ không hoạt động nhưng chúng ta vẫn có thể dùng các hàm Ngày và Giờ DAX khác để trích xuất và kết hợp lại một giá trị mới với mức độ chính xác theo giờ. Chúng ta có thể dùng công thức như thế này:

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

Cột mới của chúng tôi trông như thế này:

Cột DateTimeKey

Nếu cột Ngày của chúng ta trong bảng ngày có các giá trị theo mức độ chính xác theo giờ, thì khi đó chúng ta có thể tạo một mối quan hệ giữa chúng.

Làm cho ngày trở nên dễ dùng hơn

Nhiều cột ngày bạn tạo trong bảng ngày là cần thiết cho các trường khác nhưng thực sự không phải tất cả đều hữu ích trong việc phân tích. Ví dụ: trường DateKey trong bảng Doanh số mà chúng tôi tham chiếu và hiển thị trong toàn bộ bài viết này rất quan trọng vì đối với mọi giao dịch, giao dịch đó được ghi lại là xảy ra vào một ngày và thời gian cụ thể. Nhưng từ một điểm phân tích và báo cáo của dạng xem, không phải tất cả đều hữu ích vì chúng ta không thể dùng nó làm trường hàng, cột hoặc lọc trong Báo cáo hoặc Bảng Pivot.

Tương tự, trong ví dụ của chúng tôi, cột Ngày trong bảng Lịch rất hữu ích, rất quan trọng trong thực tế, nhưng bạn không thể sử dụng cột này làm một chiều trong PivotTable.

Để giữ cho bảng và cột trong đó càng hữu ích càng tốt, để giúp dẫn hướng danh sách Trường báo cáo Của PivotTable hoặc Power View dễ dàng hơn, bạn nên ẩn các cột không cần thiết khỏi công cụ khách. Bạn cũng có thể muốn ẩn một số bảng nhất định. Bảng Holidays hiển thị ở phần trước chứa các ngày lễ quan trọng đối với một số cột nhất định trong bảng Lịch nhưng bạn không thể sử dụng cột Ngày và Ngày lễ trong chính bảng Holidays làm trường trong PivotTable. Ở đây một lần nữa, để dẫn hướng Danh sách Trường dễ dàng hơn, bạn có thể ẩn toàn bộ bảng Ngày lễ.

Một khía cạnh quan trọng khác khi làm việc với ngày là đặt tên quy ước. Bạn có thể đặt tên cho bảng và cột trong Power Pivot bất kỳ điều gì bạn muốn. Nhưng hãy ghi nhớ, đặc biệt là nếu bạn chia sẻ sổ làm việc của mình với người dùng khác, một quy ước đặt tên tốt sẽ giúp bạn dễ dàng hơn trong việc xác định các bảng và ngày, không chỉ trong Danh sách Trường, mà còn trong Power Pivot và trong công thức DAX.

Sau khi có bảng ngày trong Mô hình Dữ liệu, bạn có thể bắt đầu tạo các số đo giúp bạn khai tác tối đa dữ liệu của mình. Một số công thức có thể đơn giản như tính tổng doanh thu cho năm hiện tại, một số khác có thể phức tạp hơn khi bạn cần lọc trên một phạm vi ngày duy nhất cụ thể. Tìm hiểu thêm trong Giá trị đo trong Power Pivot và Hàm Thông minh Thời gian.

Phụ lục

Chuyển đổi ngày tháng kiểu dữ liệu văn bản thành kiểu dữ liệu ngày

Trong một số trường hợp, bảng dữ kiện với dữ liệu giao dịch có thể chứa ngày của kiểu dữ liệu văn bản. Nghĩa là, ngày xuất hiện dưới dạng 12-12-04T11:47:09 trên thực tế không phải là ngày, hoặc ít nhất là không phải là kiểu ngày mà Power Pivot có thể hiểu được. Đó thực sự chỉ là văn bản đọc như một ngày. Để tạo mối quan hệ giữa cột ngày trong bảng dữ kiện và cột ngày trong bảng ngày tháng, cả hai cột phải thuộc kiểu dữ liệu Ngày.

Thông thường, khi bạn tìm cách thay đổi kiểu dữ liệu cho một cột ngày tháng là kiểu dữ liệu văn bản thành kiểu dữ liệu ngày, Power Pivot có thể diễn giải ngày tháng và tự động chuyển đổi thành kiểu dữ liệu ngày đúng. Nếu Power Pivot không thể chuyển đổi kiểu dữ liệu, bạn sẽ gặp lỗi kiểu không khớp.

Tuy nhiên, bạn vẫn có thể chuyển đổi ngày thành kiểu dữ liệu ngày đúng. Bạn có thể tạo cột được tính mới và dùng công thức DAX để phân tích năm, tháng, ngày, thời gian, v.v. từ các chuỗi văn bản và sau đó nối lại với nhau theo cách mà Power Pivot có thể đọc là một ngày đúng.

Trong ví dụ này, chúng tôi đã nhập một bảng dữ kiện có tên là Doanh thu vào Power Pivot. Cột này có tên là DateTime. Giá trị xuất hiện như thế này:

Cột DateTime trong bảng dữ kiện.

Nếu chúng ta xem Kiểu Dữ liệu trong tab Nhà của nhóm Định dạng của Power Pivot, chúng ta thấy rằng đó là kiểu dữ liệu Văn bản.

Kiểu dữ liệu trong ribbon

Chúng tôi không thể tạo mối quan hệ giữa cột DateTime và cột Ngày trong bảng ngày vì kiểu dữ liệu không khớp nhau. Nếu tìm cách thay đổi kiểu dữ liệu thành Ngày, chúng ta sẽ nhận được lỗi loại không khớp:

Lỗi không khớp

Trong trường hợp này, Power Pivot không thể chuyển đổi kiểu dữ liệu từ văn bản sang ngày. Chúng ta vẫn có thể dùng cột này, nhưng để chuyển nó thành kiểu dữ liệu ngày đúng, chúng ta cần tạo một cột mới phân tích cú pháp văn bản và tạo lại nó thành một giá trị mà Power Pivot có thể tạo ra kiểu dữ liệu Ngày.

Hãy nhớ rằng, từ phần Làm việc với thời gian ở phần trước của bài viết này; trừ khi nó là cần thiết để phân tích của bạn ở mức độ chính xác thời gian trong ngày, bạn nên chuyển đổi ngày trong bảng dữ kiện của bạn sang một mức độ chính xác ngày. Với suy nghĩ đó, chúng tôi muốn các giá trị trong cột mới của mình nằm ở mức độ chính xác ngày (không bao gồm thời gian). Chúng ta có thể chuyển đổi cả giá trị trong cột DateTime thành kiểu dữ liệu ngày và loại bỏ mức độ chính xác theo công thức sau đây:

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

Công cụ này cung cấp cho chúng tôi một cột mới (trong trường hợp này có tên là Date). Power Pivot thậm chí sẽ phát hiện các giá trị là ngày và đặt kiểu dữ liệu tự động thành Ngày.

Cột ngày tháng trong bảng dữ kiện

Nếu chúng ta muốn duy trì mức thời gian chính xác, chúng ta chỉ cần mở rộng công thức để bao gồm giờ, phút và giây.

=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))

Bây giờ chúng ta có một cột Ngày thuộc kiểu dữ liệu Ngày, chúng ta có thể tạo mối quan hệ giữa nó và cột ngày trong một ngày.

Các tài nguyên khác

Ngày trong Power Pivot

Phép tính trong Power Pivot

Khởi động Nhanh: Tìm hiểu Kiến thức cơ bản về DAX trong 30 Phút

Tham chiếu Biểu thức Phân tích Dữ liệu

Trung tâm Tài nguyên DAX

Bạn cần thêm trợ giúp?

Phát triển các kỹ năng của bạn
Khám phá nội dung đào tạo
Sở hữu tính năng mới đầu tiên
Tham gia Microsoft dùng nội bộ

Thông tin này có hữu ích không?

Bạn hài lòng đến đâu với chất lượng dịch thuật?
Điều gì ảnh hưởng đến trải nghiệm của bạn?

Cảm ơn phản hồi của bạn!

×