Hiển thị Trí tuệ Thời gian trong Power Pivot trong Excel

Áp dụng cho
Excel cho Microsoft 365 Excel 2024 Excel 2021 Excel 2019 Excel 2016

Biểu thức Phân tích Dữ liệu (DAX) có 35 hàm đặc biệt để tổng hợp và so sánh dữ liệu theo thời gian. Không giống như hàm ngày và thời gian của DAX, hàm Thời gian thông minh không thực sự có bất kỳ tính năng gì tương tự trong Excel. Điều này là vì các hàm hiển thị thời gian thông minh làm việc với dữ liệu thay đổi liên tục, tùy thuộc vào ngữ cảnh bạn chọn trong PivotTable và trực quan hóa Power View.

Để làm việc với các hàm hiển thị thời gian thông minh, bạn cần có một bảng ngày được bao gồm trong Mô hình Dữ liệu của bạn. Bảng ngày tháng phải bao gồm một cột với một hàng cho mỗi ngày trong mỗi năm được bao gồm trong dữ liệu của bạn. Cột này được coi là cột Ngày (mặc dù bạn có thể đặt tên là bất cứ tên nào bạn muốn). Nhiều hàm hiển thị thời gian thông minh yêu cầu cột ngày để tính toán theo ngày bạn chọn làm trường trong báo cáo. Ví dụ, nếu bạn có một số đo tính toán số dư cuối quý cuối kỳ bằng cách dùng hàm CLOSINGBALANCEQTR, để Power Pivot biết khi nào thì kết thúc quý thực sự, thì nó phải tham chiếu cột ngày tháng trong bảng ngày để biết khi nào quý bắt đầu và kết thúc. Để tìm hiểu thêm về bảng ngày tháng, hãy xem Hiểu và tạo bảng ngày tháng trong Power Pivot trong Excel.

Hàm

Hàm trả về một ngày duy nhất

Các hàm trong thể loại này trả về một ngày duy nhất. Kết quả sau đó có thể được dùng làm tham đối cho các hàm khác.

Hai hàm đầu tiên trong thể loại này trả về ngày đầu tiên hoặc cuối cùng trong Date_Column ở ngữ cảnh hiện tại. Điều này có thể hữu ích khi bạn muốn tìm ngày đầu tiên hoặc ngày cuối cùng mà bạn đã có một giao dịch thuộc một loại giao dịch cụ thể. Những hàm này chỉ chứa một đối số, tên của cột ngày trong bảng ngày tháng của bạn.

Hai hàm tiếp theo trong danh mục này tìm ngày đầu tiên hoặc ngày cuối cùng (hoặc cả bất kỳ giá trị cột nào khác) mà tại đó biểu thức có giá trị không trống. Điều này thường được sử dụng nhiều nhất trong các tình huống như hàng tồn kho, khi bạn muốn lấy số lượng hàng tồn kho cuối cùng và bạn không biết khi nào hàng tồn kho cuối cùng được thực hiện.

Thêm sáu hàm trả về một ngày duy nhất là các hàm trả về ngày đầu tiên hoặc ngày cuối cùng của một tháng, quý hoặc năm trong ngữ cảnh hiện tại của phép tính.

Hàm trả về bảng ngày tháng

Có mười sáu hàm hiển thị thời gian thông minh trả về một bảng ngày. Thông thường, các hàm này sẽ được sử dụng như một đối số SetFilter cho hàm CALCULATE . Giống như tất cả các hàm hiển thị thời gian thông minh trong DAX, mỗi hàm nhận cột ngày tháng làm một trong các đối số của nó.

Tám hàm đầu tiên trong thể loại này bắt đầu bằng cột ngày ở ngữ cảnh hiện tại. Ví dụ, nếu sử dụng số đo trong PivotTable, có thể có một tháng hoặc năm trên nhãn cột hoặc nhãn hàng. Hiệu ứng ròng là cột ngày được lọc để chỉ bao gồm ngày cho ngữ cảnh hiện tại. Bắt đầu từ ngữ cảnh hiện tại, tám hàm này sau đó tính toán ngày, tháng, quý hoặc năm trước đó (hoặc tiếp theo), rồi trả về những ngày đó dưới dạng bảng có một cột. Các hàm "previous" hoạt động ngược từ ngày đầu tiên trong ngữ cảnh hiện tại, còn các hàm "next" di chuyển tiến lên từ ngày cuối cùng trong bối cảnh hiện tại.

Bốn hàm tiếp theo trong thể loại này tương tự nhau, nhưng thay vì tính toán kỳ trước (hoặc tiếp theo), chúng tính toán tập hợp ngày trong giai đoạn "từ tháng đến ngày" (hoặc quý đến ngày, hoặc từ năm đến ngày hoặc trong cùng kỳ của năm trước). Tất cả các hàm này thực hiện tính toán của mình bằng cách sử dụng ngày cuối cùng trong bối cảnh hiện tại. Lưu ý rằng SAMEPERIODLASTYEAR yêu cầu ngữ cảnh hiện tại phải chứa một tập hợp các ngày liên tục. Nếu ngữ cảnh hiện tại không phải là tập hợp các ngày liên tục, thì SAMEPERIODLASTYEAR sẽ trả về lỗi.

Bốn hàm cuối cùng trong thể loại này phức tạp hơn một chút, nhưng cũng mạnh hơn một chút. Những hàm này được dùng để chuyển từ tập hợp ngày trong ngữ cảnh hiện tại sang bộ ngày mới.

  • DATEADD (Date_Column, Number_of_Intervals, khoảng)
  • DATESBETWEEN (Date_Column, Start_Date, End_Date)
  • DATESINPERIOD (Date_Column, Start_Date, Number_of_Intervals, Khoảng thời gian)

Hàm DATESBETWEEN tính toán tập hợp các ngày giữa ngày bắt đầu và ngày kết thúc đã chỉ định. Ba hàm còn lại dịch chuyển một số khoảng thời gian từ ngữ cảnh hiện tại. Khoảng thời gian có thể là ngày, tháng, quý hoặc năm. Các hàm này giúp dễ dàng thay đổi khoảng thời gian cho một phép tính bằng bất kỳ cách nào sau đây:

  • Quay trở lại hai năm trước
  • Quay lại một tháng
  • Tiến về phía trước ba phần tư
  • Lùi 14 ngày
  • Tiến tới 28 ngày

Trong mỗi trường hợp, bạn chỉ cần xác định khoảng nào và bao nhiêu khoảng thời gian trong số đó cần thay đổi. Khoảng dương sẽ di chuyển tiến về thời gian, trong khi khoảng âm sẽ di chuyển ngược về thời gian. Bản thân khoảng được xác định bởi một từ khóa DAY, MONTH, QUARTER hoặc YEAR. Những từ khóa này không phải là chuỗi nên không nên đặt trong dấu ngoặc kép.

Hàm đánh giá biểu thức trong một khoảng thời gian

Thể loại hàm này sẽ đánh giá một biểu thức trong một khoảng thời gian xác định. Bạn có thể thực hiện cùng một việc bằng cách sử dụng hàm CALCULATE và các hàm hiển thị thời gian thông minh khác. Ví dụ:

= TOTALMTD (biểu_thức, Date_Column [, SetFilter])

hoàn toàn giống như:

= CALCULATE (biểu_thức, DATESMTD (Date_Column)[, SetFilter])

Tuy nhiên, sẽ dễ dàng hơn khi sử dụng các hàm hiển thị thời gian thông minh này khi chúng phù hợp với vấn đề cần giải quyết:

  • TOTALMTD (Expression, Date_Column [, SetFilter])
  • TOTALQTD (Expression, Date_Column [, SetFilter])
  • TOTALYTD (Expression, Date_Column [, SetFilter] [,YE_Date]) *

Ngoài ra trong danh mục này còn có một nhóm hàm tính số dư mở cửa và cuối kỳ. Có một số khái niệm nhất định bạn nên hiểu với những hàm cụ thể này. Thứ nhất, như bạn có thể nghĩ rõ ràng, số dư đầu kỳ cho bất kỳ kỳ kỳ kỳ hạn nào cũng giống với số dư cuối kỳ cho kỳ trước. Số dư cuối kỳ bao gồm tất cả dữ liệu đến cuối kỳ, trong khi số dư đầu kỳ không bao gồm bất kỳ dữ liệu nào từ trong kỳ hiện tại.

Các hàm này luôn trả về giá trị của biểu thức được định trị cho một thời điểm cụ thể. Thời điểm mà chúng ta quan tâm luôn là giá trị ngày cuối cùng có thể có trong một khoảng thời gian lịch. Số dư đầu kỳ dựa trên ngày cuối cùng của kỳ trước, trong khi số dư cuối kỳ dựa trên ngày cuối cùng trong kỳ hiện tại. Khoảng thời gian hiện tại luôn được xác định theo ngày cuối cùng trong ngữ cảnh ngày hiện tại.

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

Bài viết: Hiểu và tạo bảng ngày tháng trong Power Pivot trong Excel

Tham khảo: Tham chiếu Hàm DAX trên Office.com

Mẫu: Lập mô hình hóa và Phân tích Dữ liệu Lãi lỗ với Microsoft PowerPivot trong Excel