Bảng ngày tháng (a date table) là một công cụ vô cùng hữu hiệu để tính toán thời gian, vì vậy nắm rõ được các thao tác tạo lập và thông tin về công cụ này sẽ giúp ích rất nhiều trong quá trình tạo dựng báo cáo. Ở bài viết này, mình sẽ mang lại cho các bạn những thông tin chi tiết và hữu ích về công cụ này.

Tạo một bảng ngày tháng (a date table) 

Trong quá trình tạo báo cáo trong Power BI, thông thường bạn sẽ được yêu cầu thực hiện các tính toán theo thời gian. Chẳng hạn, sếp của bạn muốn theo dõi tình hình kinh doanh của công ty theo tháng, theo quý, theo năm, v..v… Do vậy, việc đảm bảo rằng các dữ liệu theo ngày cần phải được định dạng đúng là vô cùng cần thiết. Power BI sẽ tự động nhận ra các cột và bảng date, tuy nhiên, mọi thứ không chỉ dừng lại ở đó. 

Ví dụ, tưởng tượng rằng bạn đang làm một báo cáo cho phòng Sales. CSDL của bạn bao gồm bảng sales, order, sản phẩm. Bạn nhận ra rằng trong một số bảng của bạn có chứa cột date riêng, chẳng hạn như cột ShipDate và OrderDate trong bảng Sales và bảng Orders. Khi đó, bạn sẽ phải làm việc với nhiều cột ngày tháng một lúc. Vậy thì các bạn sẽ làm gì? 

Để giải quyết, bạn có thể tạo ra một bảng date chung mà có thể kết nối với nhiều bảng khác nhau. Chúng ta sẽ đi sâu vào phần này ngay sau đây. 

Tạo một bảng ngày tháng chung (a common date table) 

Một số cách mà các bạn để tạo ra bảng này bao gồm: 

Dữ liệu gốc (Source data) 

Thông thường, CSDL gốc thường có sẵn một bảng date. Nếu người thiết kế ra CSDL này xây dựng bảng date một cách cẩn thận, nó sẽ có thể thực hiện các chức năng như sau: 

Bạn nên sử dụng luôn bảng date trong CSDL gốc vì nó có thể đã được tích hợp sẵn với các công cụ mà bạn sử dụng bên cạnh PBI. Trong trường hợp bạn không có sẵn bảng này, hãy sử dụng các cách sau đây: 

DAX 

Bạn có thể sử dụng hàm CALENDARAUTO() hoặc CALENDAR() để tạo một bảng date chung. Hàm CALENDAR sẽ trả về một chuỗi ngày dựa vào ngày bắt đầu và kết thúc giai đoạn, hai điểm mốc này sẽ được nhập vào hàm và coi là argument của hàm. Hàm CALENDARAUTO sẽ trả về một chuỗi ngày liên tiếp được tạo tự động theo các ngày đã có sẵn trong dataset của bạn. Ngày bắt đầu khi đó là ngày sớm nhất tồn tại trong dataset, và ngày kết thúc là ngày muộn nhất có xuất hiện trong dataset của bạn cộng với dữ liệu về năm dương lịch mà bạn có thể chọn để thêm vào. Trong trường hợp ví dụ nêu trên, bạn có thể chỉ cần dùng hàm CALENDAR để xem dữ liệu từ ngày 30/11/2011 cho đến 10 năm sau. 

Trong PBI Desktop, mở thẻ Modeling trong thanh Ribbon, chọn New Table và nhập vào công thức DAX như sau: 

Dates = CALENDAR(DATE(2011, 5, 31), DATE(2021, 5, 31))  

Như vậy bạn đã tạo được một cột chứa các ngày mà bạn cần, tuy nhiên cột này là chưa đủ. Bạn đang muốn nhìn năm, tháng, tuần, ngày, thứ, khi đó bạn có thể chọn New Column trong thanh ribbon và nhập vào hàm dưới dây. Kết quả sẽ trả về là năm của ngày trong bảng date. 

Year = YEAR(Dates[Date])  

Bạn cũng có thể làm tương tự để lấy ra tháng, tuần, ngày, thứ: 

MonthNum = MONTH(Dates[Date])  

WeekNum = WEEKNUM(Dates[Date])  

DayoftheWeek = FORMAT(Dates[Date].[Day], “DDDD”)  

Sau khi hoàn thành, bảng date của bạn sẽ trông như hình dưới đây: 

Như vậy bạn đã tạo ra được bảng date chung bằng DAX. Quá trình này chỉ bao gồm việc thêm bảng vào model, bạn vẫn phải tạo ra các relationship giữa bảng date vừa tạo và bảng Sales, bảng Orders ban đầu, sau đó đánh dấu bảng date là bảng date của cả model. Tuy nhiên, trước khi làm các thao tác đó, hãy chắc chắn rằng bạn đã cân nhắc một cách khác để tạo bảng date chung bằng Power Query. 

Bạn hoàn toàn có thể sử dụng M-language trong Power Query để tạo một bảng date chung. 

Mở Power Query từ nút Transform data trong PBI desktop. Trong khoảng trống ở cửa sổ Query phía bên tay trái, nhấn chuột phải để mở ra menu thả xuống. Chọn New Query -> Blank Query. 

Trong cửa sổ New Query, nhập dòng công thức M-code như sau: 

= List.Dates(#date(2011,05,31), 365*10, #duration(1,0,0,0)  

Với dữ liệu về sales, bạn muốn start date thể hiện ngày sớm nhất mà bạn có trong bảng sales: 31/5/2011. Hơn nữa, bạn cũng muốn quan sát 10 năm tiếp theo, bao gồm cả ngày tháng trong tương lai. Cách này sẽ giúp bạn đảm bảo rằng khi có dữ liệu mới về sales được cập nhật thì bạn cũng không cần phải làm lại bảng date. Bạn cũng có thể thay đổi đơn vị thời gian, từ đơn vị ngày, mở rộng ra đơn vị giờ, phút, giây.  

Bạn sẽ nhận ra rằng mình vừa tạo ra một danh sách các ngày chứ không phải một bảng. Để sửa lỗi, bạn mở thẻ Transform trên thanh ribbon và chọn Convert -> To Table. Khi đó, cột của bạn sẽ trở thành bảng, bạn có thể đổi tên cột thành Date Col. 

Sau đó, bạn muốn nhìn bảng date dưới dạng năm, tháng, ngày. Để làm được, bạn có thể xây dựng một hierarchy trong báo cáo của mình. Công việc đầu tiên là thay đổi định dạng cột bằng cách nhấn vào biểu tượng ở bên cạnh tên cột để mở ra một menu thả xuống, chọn Date. 

Sau đó, bạn có thể thêm cột cho năm, tháng, tuần, ngày. Mở Add Column, chọn menu thả xuống ở dưới ô Date, chọn Year, kết quả sẽ như sau: 

Lưu ý rằng Power BI đã thêm một cột của tất cả các năm được lấy từ DateCol. 

Hoàn thành quá trình tương tự trong nhiều tháng, nhiều tuần và nhiều ngàybạn đã tạo được bảng date chung bằng Power Query. 

Các bước trước đó chỉ ra cách đưa bảng vào mô hình dữ liệu. Bây giờ, bạn cần đánh dấu bảng của mình là bảng ngày chính thức để Power BI có thể nhận ra nó cho tất cả các giá trị trong tương lai và đảm bảo rằng định dạng là chính xác. 

Đánh dấu bảng ngày tháng chính thức 

Nhiệm vụ đầu tiên của bạn khi đánh dấu bảng date chính thức là tìm bảng mới trên ngăn Field. Bấm chuột phải vào tên của bảng và sau đó chọn Mark as date table 

Bằng cách đánh dấu bảng của bạn là bảng ngày, Power BI thực hiện xác thực để đảm bảo rằng dữ liệu không chứa giá trị rỗng, là duy nhất và chứa các giá trị ngày liên tục trong một khoảng thời gian. Bạn cũng có thể chọn các cột cụ thể trong bảng của mình để đánh dấu là ngày, điều này có thể hữu ích khi bạn có nhiều cột trong bảng của mình. Bấm chuột phải vào bảng, chọn Mark as Date table, sau đó chọn Date table settings. Cửa sổ mới sẽ xuất hiện, nơi bạn có thể chọn cột nào sẽ được đánh dấu là Ngày. 

Chọn Mark as date table sẽ xóa cấu trúc hierachy được tạo tự động khỏi trường Date trong bảng mà bạn đã đánh dấu là bảng ngày. Đối với các trường ngày khác, hệ thống hierachy tự động sẽ vẫn hiện diện cho đến khi bạn thiết lập mối quan hệ giữa trường đó và bảng ngày hoặc cho đến khi bạn tắt tính năng Auto Date/Time. Bạn có thể thêm hệ thống phân cấp vào bảng ngày chung của mình theo cách thủ công bằng cách bấm chuột phải vào các cột năm, tháng, tuần hoặc ngày trong ngăn Field rồi chọn New Hierarchy. Quá trình này sẽ được thảo luận thêm ở phần sau của module này. 

Dựng các biểu đồ

Để xây dựng biểu đồ của bạn giữa bảng Sales và Orders, bạn sẽ cần thiết lập mối quan hệ giữa bảng date chung mới này với bảng Sales và Đơn hàng. Do đó, bạn sẽ có thể xây dựng biểu đồ bằng cách sử dụng bảng date mới. Để hoàn thành tác vụ này, hãy chuyển đến tab Model> Manage mối quan hệ, nơi bạn có thể tạo mối quan hệ giữa bảng date, bảng Orders và Sales bằng cách sử dụng cột OrderDate. 

Tiếp theo, các bạn có thể tạo biểu đồ Total Sales and Order Quantity by Time bằng cách sử dụng chung bảng date mà bạn đã tạo. 

Để tình được tổng sales, bạn cần cộng tất cả các giá trị Sales lại bằng cách cộng tổng cột Amount như sau: 

#Total Sales = SUM(Sales[‘Amount’])  

Bạn có thể tạo một bảng mới bằng cách đổi biểu đồ: trên thẻ Visualizations, chọn Table. Bạn sẽ muốn xem tổng sales và orders theo năm và tháng, nên bạn chỉ cần đưa vào biểu đồ cột Year và Month, cột OrderQuantity và measure #TotalSales. Ở phần sau, bạn có thể biết thêm về hierarchy và trượt dọc xuống để xem chi tiết từ năm sang tháng. Trong trường hợp này, bạn có thể nhìn chúng một cách đầy đủ như sau: 

Các bạn có thể tham khảo thêm khóa học về Data Modelling & Visualization của Datapot tại đây.