Challenge PBI 260511 – Like For Like Khi Có Thay Đổi State Theo Thời Gian

Bạn là Data Analyst cho chuỗi cửa hàng ABCMart. Mỗi tháng, ban lãnh đạo cần so sánh doanh thu năm nay vs năm ngoái (PY) ở cấp cửa hàng. Vấn đề: Bạn phải loại bỏ nhiễu từ các cửa hàng mới mở / đang sửa / đã đóng.

T1-T2/24 ████████████ Hoạt động bình thường
T3/2024  ███▒░░░░░░░░ Đóng sửa từ 12/03 (Lẻ ngày)
T4-T6/24 ░░░░░░░░░░░░ Đóng trọn 3 tháng 
T7/2024  ░░░░░░░▒████ Mở lại từ 22/07 (Lẻ ngày)
T8/2024  ████████████ Hoạt động bình thường

[2023 - Sửa chữa]
T7/2023  ████████████ Mở bình thường
T8/2023  █▒░░░░░░░░░░ Đóng sửa (05/08)
T9/2023  ░░░░░░░░░░░░ Đóng trọn tháng
T10/2023 ░░░░░░░░░░▒█ Mở lại (29/10)

[2024 - Đóng vĩnh viễn]
T7/2024  ████████████ Mở bình thường
T8/2024  ████▒░░░░░░░ Đóng vĩnh viễn (14/08)
T9-T12   ░░░░░░░░░░░░ Không có doanh thu

T1-T4/24 ░░░░░░░░░░░░ (Chưa tồn tại)
T5/2024  ░░░▒████████ Khai trương (08/05)
T6/2024  ████████████ Mở bình thường

T8/2024  ████████████ Mở bình thường
T9/2024  █▒░░░░░░░░░░ Đóng sửa (03/09)
T10/2024 ░░░░░░░░░░░░ Đóng trọn tháng
T11/2024 ░░░░░░░▒████ Mở lại (16/11)

Mỗi cặp (cửa hàng + tháng) sẽ được phân loại vào 1 trong 4 trạng thái:

#	Trạng thái	Ý nghĩa
1	Comparable	Hoạt động bình thường cả 2 năm → so sánh được
2	Opening	Mới mở, năm ngoái chưa có
3	Closing	Đóng vĩnh viễn trong kỳ
4	Refresh	Tạm đóng sửa chữa

• Tháng 3/2024, TanPhu đóng sửa từ ngày 12/03. Trong 11 ngày đầu tháng, store vẫn bán được 138 triệu. Trạng thái tháng này là Refresh.
• Tương ứng năm trước, T3/2023, TanPhu hoạt động bình thường cả tháng → bán được 338 triệu. Trạng thái lúc đó là Comparable.
• → Cùng một cửa hàng, cùng tháng 3, nhưng trạng thái ở 2 năm khác nhau.

Trường hợp báo cáo lỗi

┌───────────────┬──────────┬─────────────────┬─────────────────┐
│ L4L State     │ Cửa hàng │ DT 2024 (nay)   │ DT 2023 (PY)    │
├───────────────┼──────────┼─────────────────┼─────────────────┤
│ Refresh       │ TanPhu   │ 138 triệu       │ (trống)         │
│               │          │ → đúng: 2024    │ → SAI: PY đáng  │
│               │          │ đang sửa        │ lẽ ở đây        │
├───────────────┼──────────┼─────────────────┼─────────────────┤
│ Comparable    │ TanPhu   │ (trống)         │ 338 triệu       │
│               │          │                 │ → Lỗi: PY văng  │
│               │          │                 │ sang Comparable │
│               │          │                 │ vì 2023 = Comp. │
└───────────────┴──────────┴─────────────────┴─────────────────┘

Hậu quả: BLĐ lọc nhóm Comparable để đánh giá sức khỏe cửa hàng bình thường → con số 338 triệu PY của TanPhu bị lọt vào → tỷ lệ tăng trưởng sai sự thật.

Trường hợp báo cáo đúng

┌───────────────┬──────────┬─────────────────┬─────────────────┐
│ L4L State     │ Cửa hàng │ DT 2024 (nay)   │ DT 2023 (PY)    │
├───────────────┼──────────┼─────────────────┼─────────────────┤
│ Refresh       │ TanPhu   │ 138 triệu       │ 338 triệu       │
├───────────────┼──────────┼─────────────────┼─────────────────┤
│ Comparable    │ TanPhu   │ (trống)         │ (trống)         │
└───────────────┴──────────┴─────────────────┴─────────────────┘

Insight: T3/2024 TanPhu chỉ bán 138 triệu, trong khi cùng kỳ năm ngoái bán 338 triệu → thiệt hại khoảng 200 triệu do đóng sửa 20 ngày.

• Tháng 8/2024, CauGiay đóng cửa vĩnh viễn từ ngày 14/08. Trong 13 ngày đầu tháng, store bán được 184 triệu. Trạng thái: Closing.
• Tương ứng T8/2023, CauGiay lúc đó đang đóng sửa (Refresh), chỉ bán được 4 ngày → 50 triệu. Trạng thái năm đó là Refresh.
• → Cả 2 năm đều không phải Comparable, nhưng lý do khác nhau (Closing vs Refresh).

Trường hợp báo cáo lỗi
│ Closing       │ CauGiay  │ 184 triệu       │ (trống)         │
│ Refresh       │ CauGiay  │ (trống)         │ 50 triệu        │
→ CY và PY ở 2 dòng khác nhau → không so sánh được

Trường hợp báo cáo đúng
│ Closing       │ CauGiay  │ 184 triệu       │ 50 triệu        │
→ Insight: T8 CY bán 184 tr (13 ngày), PY chỉ 50 tr (4 ngày, đang sửa) → CY cao hơn PY

Khi user lọc báo cáo theo một trạng thái, cả doanh thu năm nay lẫn doanh thu năm ngoái (PY) phải nằm cùng dòng trạng thái đó.

Một cửa hàng có thể xuất hiện nhiều dòng nếu nó từng trải qua sự kiện đóng sửa hoặc đóng cửa vĩnh viễn.

store_key	store_name	city	open_date	close_date	l4l_key
101	ABCMart Q1 HCM	TP.HCM	2019-06-15		1
102	ABCMart TanPhu HCM	TP.HCM	2019-03-10		1
102	ABCMart TanPhu HCM	TP.HCM	2024-03-12	2024-07-22	4
103	ABCMart CauGiay HN	Hà Nội	2020-02-18		1
103	ABCMart CauGiay HN	Hà Nội	2023-08-05	2023-10-28	4
103	ABCMart CauGiay HN	Hà Nội		2024-08-14	3
104	ABCMart LongBien HN	Hà Nội	2024-05-08		2
105	ABCMart HaiChau DN	Đà Nẵng	2018-11-20		1
106	ABCMart NgoQuyen HP	Hải Phòng	2021-04-01		1
107	ABCMart NinhKieu CT	Cần Thơ	2020-07-01		1
107	ABCMart NinhKieu CT	Cần Thơ	2024-09-03	2024-11-15	4

• Cửa hàng bình thường (101, 105, 106): Chỉ có 1 dòng, l4l_key = 1 (Comparable), close_date = NULL (chưa bao giờ đóng).
• Cửa hàng có thay đổi sự kiện (102, 103, 107): Có dòng gốc (l4l_key = 1) cộng thêm dòng riêng cho mỗi sự kiện. Ví dụ cửa hàng 102 (TanPhu) có 2 dòng:
  • Dòng 1: thông tin gốc (mở từ 2019, chưa đóng)
  • Dòng 2: sự kiện đóng sửa chữa (12/03/2024 → 22/07/2024), l4l_key = 4 (Refresh)
• Cửa hàng mới (104 — LongBien): Chỉ 1 dòng, l4l_key = 2 (Opening), open_date = ngày khai trương.

l4l_key	l4l_state	l4l_description
1	Comparable	Hoạt động bình thường cả 2 năm → so sánh được
2	Non-Comparable – Opening	Cửa hàng mới mở, năm ngoái chưa có
3	Non-Comparable – Closing	Đóng vĩnh viễn trong kỳ
4	Non-Comparable – Refresh	Tạm đóng để sửa chữa / nâng cấp

sales_date	store_key	sales_amount
2023-01-01	101	15,120,000
2023-01-01	102	11,340,000
…	…	…
2024-07-23	102	8,930,000
…	…	…

• Mỗi dòng = doanh thu 1 ngày của 1 cửa hàng (đã gộp từ hàng nghìn giao dịch).
• Ngày cửa hàng đóng cửa sẽ không có dòng trong bảng này. Không có dữ liệu khác với bán được 0 đồng.

• Bảng: _ref_store thì mỗi cửa hàng có 1 hoặc nhiều dòng. Nếu nối trực tiếp bảng này với fact_retail_sales, mỗi giao dịch của cửa hàng sẽ nhân lên theo số dòng → doanh thu sai.
• Nhưng nếu chỉ giữ lại mỗi dòng unique cho từng cửa hàng thì sẽ mất thông tin sự kiện (ngày đóng sửa, ngày đóng cửa). Không biết tháng nào là Comparable, tháng nào là Refresh → không thể so sánh L4L.

Giải pháp: Tạo bảng bridge_l4l. Mỗi dòng ghi rõ trạng thái L4L của 1 cửa hàng trong 1 tháng cụ thể:

Ví dụ — TanPhu (store_key = 102):
  T1-T2/2024:   l4l_key = 1 (Comparable)  → trước sửa chữa
  T3-T7/2024:   l4l_key = 4 (Refresh)     → đang đóng sửa
  T8-T12/2024:  l4l_key = 1 (Comparable)  → sau sửa chữa, hoạt động bình thường

• Click phải vào _ref_store → Enable load → bỏ tick ✗ (không load vào model) → Bảng này chỉ dùng trong Power Query (làm nguồn cho bridge). Không xuất hiện trong Model View.

• .Vào New Source → Blank Query → Đổi tên lại thành dim_store → Viết M Code trên thanh fx

= Table.Distinct(
        Table.SelectColumns(_ref_store, {"store_key", "store_name", "city"})
    )

• Bảng fact_retail_sales → Add Column → Custom Column → Đặt tên bridge_key

[store_key] * 1000000 + Date.Year([sales_date]) * 100 + Date.Month([sales_date])

• Vào New Source → Blank Query

Trên thanh fx
= Date.StartOfMonth(List.Min(fact_retail_sales[sales_date]))

Click fx
= Date.StartOfMonth(List.Max(fact_retail_sales[sales_date]))

Click fx
= (Date.Year(MaxDate) - Date.Year(MinDate)) * 12
                 + Date.Month(MaxDate) - Date.Month(MinDate) + 1

Click fx
= List.Generate(
        () => MinDate,
        each _ < Date.AddMonths(MinDate, MonthCount),
        each Date.AddMonths(_, 1)
    )

• Power Query UI → Chọn To Table
• Power Query UI → Add Column → Date → Chọn End of Month

Add Column → Custom Column → YearMonth
Date.Year([FirstDayOfMonth]) * 100
            + Date.Month([FirstDayOfMonth])

Cột	Mục đích	Tại sao cần
FirstDayOfMonth	Kiểm tra chồng lấn	Biên trái của tháng. VD: sự kiện kết thúc 22/07 ≥ 01/07? → có chồng lấn
LastDayOfMonth	Kiểm tra chồng lấn	Biên phải của tháng. VD: sự kiện bắt đầu 12/03 ≤ 31/03? → có chồng lấn
YearMonth		Mã tháng dạng số (202403) — dùng để tạo bridge_key = store_key * 1000000 + YearMonth

• Click phải _ref_store → Reference → đặt tên _refresh_events
• Click dropdown cột l4l_key → bỏ tick tất cả → chỉ tick 4 → OK
• Click phải _refresh_events → Enable load → bỏ tick ✗

Tại sao phải có query này? → Minh họa từng bước:

store_key | open_date   | close_date  | l4l_key | Loại
101       | 2019-06-15  |             | 1       | ← Comparable (bỏ qua)
102       | 2019-03-10  |             | 1       | ← Comparable (bỏ qua)
102       | 2024-03-12  | 2024-07-22  | 4       | ← Refresh ✓ GIỮ LẠI
103       | 2020-02-18  |             | 1       | ← Comparable (bỏ qua)
103       | 2023-08-05  | 2023-10-28  | 4       | ← Refresh ✓ GIỮ LẠI
103       |             | 2024-08-14  | 3       | ← Closing (bỏ qua — xử lý riêng)
104       | 2024-05-08  |             | 2       | ← Opening (bỏ qua — xử lý riêng)
105       | 2018-11-20  |             | 1       | ← Comparable (bỏ qua)
106       | 2021-04-01  |             | 1       | ← Comparable (bỏ qua)
107       | 2020-07-01  |             | 1       | ← Comparable (bỏ qua)
107       | 2024-09-03  | 2024-11-15  | 4       | ← Refresh ✓ GIỮ LẠI

Sau lọc l4l_key = 4
store_key | open_date   | close_date  | l4l_key
102       | 2024-03-12  | 2024-07-22  | 4      
103       | 2023-08-05  | 2023-10-28  | 4      
107       | 2024-09-03  | 2024-11-15  | 4      

Ví dụ — TanPhu (102), sự kiện: [12/03 ═══════ 22/07]

Tháng 2/2024 [01/02 ─── 29/02]:
  12/03 ≤ 29/02? → KHÔNG chồng lấn → Comparable (1)

Tháng 3/2024 [01/03 ─── 31/03]:
  12/03 ≤ 31/03? và 22/07 ≥ 01/03? → CÓ chồng lấn → Refresh (4)
  ████████████░░░░░░░░░░░░░░░░░░░
  bán 11 ngày │ đóng sửa 20 ngày

Tháng 4/2024 [01/04 ─── 30/04]:
  12/03 ≤ 30/04? và 22/07 ≥ 01/04? → CÓ chồng lấn → Refresh (4)
  ░░░░░░░░░░░░░░░░░░░░░░░░░░░░░░
  đóng sửa trọn tháng (doanh thu = 0)

Tháng 8/2024 [01/08 ─── 31/08]:
  12/03 ≤ 31/08?  nhưng  22/07 ≥ 01/08? → KHÔNG → Comparable (1)
  ████████████████████████████████
  đã mở lại, bán bình thường

Ví dụ — Opening: LongBien (104), khai trương 08/05/2024
Không dùng chồng lấn — vì cửa hàng mới chưa có PY nào cả.
→ Gán TOÀN BỘ 24 tháng = 2 (Opening), bất kể tháng nào.

T1/2023: Opening (2) — chưa tồn tại, không có doanh thu
T5/2024: Opening (2) — khai trương ngày 8, bắt đầu bán
T12/2024: Opening (2) — vẫn Opening vì chưa đủ 1 năm để so sánh PY

→ Nếu store_key ∈ danh sách Opening → ghi đè tất cả = 2.

Ví dụ — Closing: CauGiay (103), đóng vĩnh viễn 14/08/2024
Không dùng chồng lấn — vì cần gán TỪ tháng đóng ĐẾN HẾT.

T7/2024 [01/07 ─── 31/07]:
  close_date (14/08) ≤ 31/07? → chưa đóng → giữ nguyên trạng thái cũ

T8/2024 [01/08 ─── 31/08]:
  close_date (14/08) ≤ 31/08? → ĐÃ ĐÓNG → Closing (3)
  ██████████████░░░░░░░░░░░░░░░░░░
  bán 14 ngày  │ đóng vĩnh viễn

T9/2024 → T12/2024:
  close_date (14/08) ≤ cuối tháng? → Closing (3) — tiếp tục đóng

→ Nếu close_date ≤ cuối tháng đang xét → gán = 3 từ đó đến hết.

• Vào New Source → Blank Query. Mỗi bước dưới đây = 1 applied step trong Power Query.

Click vào thanh fx
= Table.Distinct(Table.SelectColumns(_ref_store, {"store_key"}))

Giải thích:
- Table.SelectColumns(bảng, {"cột"}) — chọn cột, bỏ cột còn lại. Giống "Remove Other Columns" trong UI.
- Table.Distinct(bảng) — xóa dòng trùng. CauGiay có 3 dòng giống nhau → chỉ giữ 1.
- Hai hàm lồng nhau: SelectColumns chạy trước (bên trong), rồi Distinct chạy sau (bên ngoài).
- _ref_store — tên query khác. M code cho phép tham chiếu query bằng tên, giống gọi hàm.

Add Column → Custom Column → tên: MonthData
= ListMonths

Giải thích:
- Khi nhập = ListMonths trong Custom Column, Power Query gắn toàn bộ bảng ListMonths vào mỗi dòng store.
- Đây là kỹ thuật nested table — mỗi ô chứa 1 bảng con bên trong. Nhìn sẽ thấy chữ "Table" ở mỗi ô.
- Custom Column luôn chạy each ngầm — tức "cho mỗi dòng, thực hiện formula này".

• Click icon ↔ ở header cột MonthData → tick tất cả cột → OK.

Add Column → Custom Column → tên: MatchedState
let
            sk = [store_key],
            firstDay = [FirstDayOfMonth],
            lastDay  = [LastDayOfMonth],
            matched = Table.SelectRows(_refresh_events, each
                [store_key] = sk
                and [open_date]  <= lastDay
                and [close_date] >= firstDay
            )
        in
            if Table.RowCount(matched) > 0
            then matched{0}[l4l_key]
            else 1

Giải thích:
- let ... in ... — cho phép viết nhiều bước trong 1 ô Custom Column. Không có let, chỉ được viết 1 dòng.
- sk = [store_key] — tạo biến cục bộ (chỉ tồn tại trong ô này). Tại sao cần? Vì [store_key] bên trong Table.SelectRows sẽ bị hiểu là cột của _refresh_events, không phải cột dòng hiện tại. Gán vào biến sk trước → tránh nhầm.
Table.SelectRows(bảng, each ...) — lọc dòng. each ở đây là each lồng (bên trong each ngầm của Custom Column). Nghĩa là: "cho mỗi dòng của bảng 168 dòng → cho mỗi dòng của _refresh_events, kiểm tra điều kiện".
and — toán tử logic "VÀ". M code dùng and / or (viết thường), không dùng && / ||.
matched{0}[l4l_key] — {0} = lấy dòng đầu tiên (index bắt đầu từ 0). [l4l_key] = lấy giá trị cột l4l_key ở dòng đó.
Table.RowCount(bảng) — đếm số dòng. Nếu = 0 = không tìm thấy biến cố nào chồng lấn.

Click thanh fx (thêm step mới) -> tên: OpeningStores
= List.Buffer(
        Table.Column(
            Table.SelectRows(_ref_store, each [l4l_key] = 2),
            "store_key"
        )
    )

Giải thích:
- List vs Table: Table = bảng nhiều cột. List = danh sách 1 chiều (chỉ chứa giá trị). VD: {101, 104, 107}.
- Table.SelectRows(bảng, each điều_kiện) — lọc dòng. each = "cho mỗi dòng, kiểm tra điều kiện".
- Table.Column(bảng, "tên_cột") — trích 1 cột từ bảng ra thành List. VD: bảng 1 dòng có store_key=104 → List {104}.
- List.Buffer(list) — load toàn bộ list vào RAM 1 lần. Bước sau dùng List.Contains sẽ nhanh hơn (không phải tính lại).

Add Column → Custom Column → tên: StateWithOpening
if List.Contains(OpeningStores, [store_key])
        then 2
        else [MatchedState]

Giải thích:
- if ... then ... else ... — giống IF trong Excel. Bắt buộc phải có else (không được bỏ trống).
List.Contains(danh_sách, giá_trị) — kiểm tra giá trị có trong List không. Trả về true / false.
[MatchedState] — tham chiếu cột đã tạo ở bước trước. M code cho phép dùng cột từ bước trước.

Click fx -> ClosingInfo
= Table.SelectRows(_ref_store, each [l4l_key] = 3)

Add Column → Custom Column → l4l_key
let
            currentStore   = [store_key],
            currentLastDay = [LastDayOfMonth],
            closeMatch = Table.SelectRows(ClosingInfo, each
                [store_key]  = currentStore
                and [close_date] <= currentLastDay
            )
        in
            if Table.RowCount(closeMatch) > 0 then 3
            else [StateWithOpening]

Add Column → Custom Column → bridge_key
Add Column → Custom Column → l4l_key
= [store_key] * 1000000 + [year_month]

• Power Query UI → Home → Choose Columns → Chọn {“store_key”, “year_month”, “l4l_key”, “bridge_key”}