---
title: "เจาะลึก CALCULATE ใน DAX แบบลึกสุดใจ : Part 2"
url: https://www.thepexcel.com/calculate-dax-in-depth-pt2/
type: post
date: 2022-09-11
updated: 2022-11-02
author: Sira Ekabut
tags: [CALCULATE-DAX, DAX Formula, dax]
---

# เจาะลึก CALCULATE ใน DAX แบบลึกสุดใจ : Part 2

ตอนนี้เป็นเนื้อหาที่ต่อจาก [https://www.thepexcel.com/calculate-dax-in-depth/](https://www.thepexcel.com/calculate-dax-in-depth/) ซึ่งจำเป็นจะต้องอ่านก่อน ไม่งั้นไม่มีทางเข้าใจบทความนี้ได้เลยครับ ถ้าอ่านบทความก่อนจบแล้ว ก็เชิญมาต่อที่นี่ได้เลย

 

ใน Part2 นี้ผมจะปูความรู้ 2 เรื่องที่สำคัญมากๆ นั่นคือ Context Transition และ CALCULATETABLE ให้ก่อน ใน Part ถัดไปจะเอาทุกอย่างมายำกันละ

 

## Context Transition

 

### ทำความเข้าใจ Context Transition เบื้องต้น

 

ปกติแล้วถ้าเราเขียนสูตรใน New Column ของตาราง มันจะไม่มี Filter อะไรเกิดขึ้นเลย ดังนั้นถ้าเราอยู่ที่ตาราง Store แล้วเขียน SUM Quantity ก็จะได้ค่าเท่ากันหมดเลยเสมอ ซึ่งก็คือการ SUM คอลัมน์ที่เราเลือกทั้งตารางนั่นเอง

 

```
Store Qty = SUM(OrderDetail[Quantity])
```

 ![1](https://www.thepexcel.com/wp-content/uploads/2022/09/calculate-pt2-001-1024x440.png) 

แต่ถ้าหากเราเอา CALCULATE ไปครอบสูตรนั้น ผลลัพธ์จะเปลี่ยนไป กลายเป็นว่าเราได้ Qty เฉพาะร้านค้านั้นๆ

 

```
Store Qty = CALCULATE(SUM(OrderDetail[Quantity]))
```

 ![2](https://www.thepexcel.com/wp-content/uploads/2022/09/calculate-pt2-002-1024x376.png) 

สาเหตุคือ CACLCULATE จะทำการ**เปลี่ยนค่าในแต่ละคอลัมน์ของแถวนั้นๆ ทุกคอลัมน์ (Row Context) ให้กลายเป็น Filter Context** ซึ่งกระบวนการนี้เรียกว่า Context Transition (นั่นคือจะมี Filter เกิดขึ้นมากมายตามจำนวนคอลัมน์ของข้อมูลเลย)

 

ทำให้เดิมทีที่ไม่มี Filter ใน Table กลายเป็นมี Filter ก่อนจะคำนวณ SUM(OrderDetail[Quantity]) ออกมา เราก็เลยได้ Quantity เฉพาะร้านค้านั้นๆ ไปโดยปริยาย ซึ่งก็เจ๋งดี

 

แต่ความแปลกที่แท้จริง คือ ถ้าเราเปลี่ยนสูตรไปอ้างอิง Measure Total Qty แทน ซึ่งมีสูตรดังนี้

 

```
Total Qty = SUM(OrderDetail[Quantity])
```

 

เช่น ในคอลัมน์ Store Qty เราเปลี่ยนสูตรเป็นแบบนี้

 

```
Store Qty = [Total Qty]
```

 

เราจะได้ผลลัพธ์เหมือนมี CALCULATE มาครอบทันที!! ซึ่งเกิดขึ้นเพราะว่า**เมื่อใดก็ตามที่เราอ้างอิง Measure มันจะแอบเอา CALCULATE มาครอบให้โดยอัตโนมัติ!**

 ![3](https://www.thepexcel.com/wp-content/uploads/2022/09/calculate-pt2-003-1024x368.png) 

### Context Transition ใน Measure แบบพื้นฐาน

 

อย่างไรก็ตาม ปกติแล้วเราจะไม่ค่อยใช้ Context Transition ในตารางที่มีตัวตนจริงๆ หรอก **แต่เราจะใช้ในตารางจำลองที่สร้างใน Measure อีกที**ต่างหาก โดยเฉพาะเวลาใช้กับ Iterator เช่น SUMX, MAXX, RANKX, FILTER, ADDCOLUMNS อะไรแบบนี้เป็นต้น

 

พฤติกรรมแบบนี้ทำให้เราสามารถเขียนสูตรที่ซับซ้อนได้โดยเขียนสูตรที่สั้นขึ้นมากๆ เช่น สมมติผมต้องการคำนวณ **Total Qty สูงสุดในระดับ Product** ผมสามารถเขียน Measure สั้นๆ แบบนี้ได้เลย

 

```
MAX Qty per Product = MAXX('Product',[Total Qty])
```

 
- สิ่งที่มันทำคือจะสร้างตาราง Product ขึ้นมาในสูตร (1 บรรทัด 1 Product)
- แล้ว Add Column จำลองใหม่ขึ้นมา โดยใช้สูตร [Total Qty]
- ซึ่งจะได้ Quantity รวมโดยมี Context Transition เกิดขึ้น
- คอลัมน์จำลอง เลยได้ Quantity รวมในระดับ Product เกิดขึ้นมาในแต่ละแถว
- จากนั้นหาค่ามากสุดออกมาเพราะเราใช้ **MAX**X (ตอนจบ หาค่ามากสุดของคอลัมน์จำลอง)

 

แล้วเราก็สามารถเอา Measure นี้ไปใช้ในรายงาน โดย Filter ด้วยอะไรก็ได้ (ตราบเท่าที่มัน Filter เจ้าตัว [Total Qty] ได้)

 ![4](https://www.thepexcel.com/wp-content/uploads/2022/09/calculate-pt2-004.png) 

แต่ถ้าเราต้องการ Max Total Qty ในระดับที่ใหญ่กว่าระดับที่เรามีในตารางจริง เราจะใช้การอ้างอิงตารางทื่อๆ ไม่ได้ แต่ต้องสร้างตารางจำลองขึ้นมาในสูตรแทน ซึ่งทำได้ง่ายๆ ด้วย DISTINCT หรือ VALUES นั่นเอง เช่น

 

ผมต้องการ คำนวณ **Total Qty สูงสุดในระดับ Brand** ผมก็เขียนแบบนี้ได้เลย

 

```
MAX Qty per Brand = MAXX(DISTINCT('Product'[Brand]),[Total Qty])
```

 ![5](https://www.thepexcel.com/wp-content/uploads/2022/09/calculate-pt2-005-1024x386.png) 

ซึ่งจริงๆ แล้วต่อให้เราอยากได้ในระดับ Product **หากเราใช้ DISTINCT หรือ VALUES จะทำให้คำนวณเร็วกว่าการอ้างอิงทั้งตาราง**แน่นอน ดังนั้นจึงสามารถแก้สูตรเดิมเป็นอันนี้ก็จะดีขึ้นครับ

 

```
MAX Qty per Product = MAXX(DISTINCT('Product'[ProductKey]),[Total Qty])
```

 

#### ทำความเข้าใจสิ่งที่เกิดขึ้น

 

สมมติผมจะวิเคราะห์ผลลัพธ์ใน MAX Qty per Brand ของลูกค้าทวีป Asia ในรายงาน ด้วยสูตรนี้

 

```
MAX Qty per Brand = MAXX(DISTINCT('Product'[Brand]),[Total Qty])
```

 

หลังจากมันสร้างตารางจำลองระดับ Brand ขึ้นมา ให้ 1 บรรทัดคือ 1 Brand แล้วสร้างคอลัมน์ใหม่ที่ใส่สูตรว่า [Total Qty] ลงไป

 

สมมติว่า ผมจำลองการมองที่บรรทัดของ “**ตารางจำลอง**” ที่ Brand เป็น Litware ให้ดู

 

**แบ่งกลุ่ม**

 
1. Filter Context ดั้งเดิมของจุดที่สนใจ (รวมถึง Filter Context ของ CALCULATE ชั้นนอกด้วย)
    - Customer[Cust Continent]=”Asia”
2. Row Context ดั้งเดิม
    - ‘Product'[ฺBrand]=”Litware” (เกิดในตารางจำลอง)
3. เงื่อนไข Filter Argument (ที่จริงๆ คือตาราง) ที่ตั้งใจเขียนใน CALCULATE ตัวนั้นๆ 
    - ไม่มี เพราะสูตรมีแค่ CALCULATE(SUM(OrderDetail[Quantity]))
4. CALCULATE Modifier เช่น REMOVEFILTERS, ALL, ALLSELECTED, ALLEXCEPT, USERELATIONSHIP, CROSSFILTER
    - ไม่มี เพราะสูตรมีแค่ CALCULATE(SUM(OrderDetail[Quantity]))

 

**ขั้นตอนการทำงาน**

 
- เก็บ **Filter Argument** เอาไว้ในใจ
    - ไม่มี
- Copy Filter Context เดิมมาเป็น New Filter Context
    - Customer[Cust Continent]=”Asia”
- Row Context
    - ‘Product'[ฺBrand]=”Litware“ => ถูกทำให้เป็นส่วนหนึ่งของใน New Filter Context
- CALCULATE Modifier
    - ไม่มี
- เอา **Filter Argument** ที่เก็บไว้มาใช้ สรุปแล้ว New Filter Context ได้แบบนี้
    - Customer[Cust Continent]=”Asia”
    - ‘Product'[ฺBrand]=”Litware“

 

ดังนั้นในตารางจำลองบรรทัดที่เป็น Litware จึงมีผล Filter จากทั้ง Brand Litware และจากทวีป Asia ด้วยนั่นเอง

 

ดังนั้น ตารางจำลองที่ถูกสร้างขึ้นมามันจะคล้ายกับ Table อันนี้มากๆ ซึ่งค่าที่มากสุดคือ 38,384 นั่นเอง

 ![6](https://www.thepexcel.com/wp-content/uploads/2022/09/calculate-pt2-006.png) 

แปลว่าคอลัมน์ใหม่ในตารางจำลองทั้งหมด ได้รับผล Filter จากทวีปนั้นๆ อย่างถูกต้อง จึงออกมาเป็น Total Qty ภายใต้ทวีปและ brand นั้นๆ ผลลัพธ์ตอน MAXX ที่ได้จึงเป็น Max Total Qty per Brand ภายใต้แต่ละทวีปอย่างถูกต้อง ซึ่งก็คือ 38,384 นั่นเองครับ

 

### Context Transition ใน Measure แบบซับซ้อนขึ้น

 

ทีนี่ถ้าใน Measure ที่เราทำ Context Transition ดันมี CALCULATE ที่มีเงื่อนไขอยู่แล้วล่ะ? บอกไว้ก่อนว่าอันนี้ทำเพื่อการศึกษาเท่านั้น ในชีวิตจริงบาง Measure ที่เขียนในนี้จะดูไม่ Make Sense มากๆ 55

 

#### Case ที่ 1 : เขียนเงื่อนไขที่ไม่ซ้ำกับ Row Context

 

ถ้าเราใช้ Measure นี้ใน Context Transition

 

```
Person Cust Total Qty = CALCULATE([Total Qty],Customer[Customer Type]="Person")
```

 

เช่นสมมติว่า ผมเขียน Measure ที่จะหาว่า Quantity รวมของลุกค้าบุคคลที่เยอะที่สุดต่อ 1 Brand คือเท่าไหร่ ซึ่งเขียนได้แบบนี้

 

```
MAX Person Qty per Brand = MAXX(DISTINCT('Product'[Brand]),[Person Cust Total Qty])
```

 

ดังนั้นการวิเคราะห์ การมองที่บรรทัดของ “**ตารางจำลอง**” ที่ Brand เป็น Litware จะเป็นแบบนี้

 

**แบ่งกลุ่ม**

 
1. Filter Context ดั้งเดิมของจุดที่สนใจ (รวมถึง Filter Context ของ CALCULATE ชั้นนอกด้วย)
    - Customer[Cust Continent]=”Asia”
2. Row Context ดั้งเดิม
    - ‘Product'[ฺBrand]=”Litware” (เกิดในตารางจำลอง)
3. เงื่อนไข Filter Argument (ที่จริงๆ คือตาราง) ที่ตั้งใจเขียนใน CALCULATE ตัวนั้นๆ 
    - Customer[Customer Type]=”Person” (จาก CALCULATE ใน Measure)
4. CALCULATE Modifier เช่น REMOVEFILTERS, ALL, ALLSELECTED, ALLEXCEPT, USERELATIONSHIP, CROSSFILTER
    - ไม่มี

 

**ขั้นตอนการทำงาน**

 
- เก็บ **Filter Argument** เอาไว้ในใจ
    - Customer[Customer Type]=”Person”
- Copy Filter Context เดิมมาเป็น New Filter Context
    - Customer[Cust Continent]=”Asia”
- Row Context
    - ‘Product'[ฺBrand]=”Litware“ => ถูกทำให้เป็นส่วนหนึ่งของใน New Filter Context
- CALCULATE Modifier
    - ไม่มี
- เอา **Filter Argument** ที่เก็บไว้มาใช้ สรุปแล้ว New Filter Context ได้แบบนี้
    - Customer[Cust Continent]=”Asia”
    - ‘Product'[ฺBrand]=”Litware“
    - Customer[Customer Type]=”Person”

 

ดังนั้นในตารางจำลองบรรทัดที่เป็น Litware จึงมีผล Filter จากทั้ง Brand Litware และจากทวีป Asia และมาจากลูกค้าบุคคล ด้วยนั่นเอง

 

#### Case ที่ 2 : มีการปลด Filter ที่ Row Context ออก

 

ถ้าเราใช้ Measure นี้ใน Context Transition

 

```
Total Qty All Brand = CALCULATE([Total Qty],REMOVEFILTERS('Product'[Brand]))
```

 

เช่นสมมติว่า ผมเขียน Measure ที่จะหาว่า Quantity รวมของลุกค้าบุคคลที่เยอะที่สุดต่อ 1 Brand คือเท่าไหร่ ซึ่งเขียนได้แบบนี้

 

```
MAX Person Qty per Brand = MAXX(DISTINCT('Product'[Brand]),[Total Qty All Brand])
```

 

ดังนั้นการวิเคราะห์ การมองที่บรรทัดของ “**ตารางจำลอง**” ที่ Brand เป็น Litware จะเป็นแบบนี้

 

**แบ่งกลุ่ม**

 
1. Filter Context ดั้งเดิมของจุดที่สนใจ (รวมถึง Filter Context ของ CALCULATE ชั้นนอกด้วย)
    - Customer[Cust Continent]=”Asia”
2. Row Context ดั้งเดิม
    - ‘Product'[ฺBrand]=”Litware” (เกิดในตารางจำลอง)
3. เงื่อนไข Filter Argument (ที่จริงๆ คือตาราง) ที่ตั้งใจเขียนใน CALCULATE ตัวนั้นๆ 
    - ไม่มี
4. CALCULATE Modifier เช่น REMOVEFILTERS, ALL, ALLSELECTED, ALLEXCEPT, USERELATIONSHIP, CROSSFILTER
    - REMOVEFILTERS(‘Product'[Brand])

 

**ขั้นตอนการทำงาน**

 
- เก็บ **Filter Argument** เอาไว้ในใจ
    - ไม่มี
- Copy Filter Context เดิมมาเป็น New Filter Context
    - Customer[Cust Continent]=”Asia”
- Row Context
    - ‘Product'[ฺBrand]=”Litware“ => ถูกทำให้เป็นส่วนหนึ่งของใน New Filter Context
- CALCULATE Modifier
    - REMOVEFILTERS(‘Product'[Brand]) ทำให้ New Filter Context เรื่อง Brand หายไป
- เอา **Filter Argument** ที่เก็บไว้มาใช้ สรุปแล้ว New Filter Context ได้แบบนี้
    - Customer[Cust Continent]=”Asia”

 

ดังนั้นในตารางจำลองบรรทัดที่เป็น Litware จึงมีผล Filter จากทวีป Asia อย่างเดียว ผลที่ได้จึงไม่ต่างจากการใช้ Total Qty เฉยๆ นั่นเอง

 

#### Case ที่ 3 : เขียนเงื่อนไขซ้ำกันกับ Row Context

 

ถ้าเราใช้ Measure นี้ใน Context Transition

 

```
Contoso Total Qty = CALCULATE([Total Qty],'Product'[Brand]="Proseware")
```

 

เช่นสมมติว่า ผมเขียน Measure ที่จะหาว่า Quantity รวมของ Brand Proseware ที่เยอะที่สุดต่อ 1 Brand คือเท่าไหร่ ซึ่งเขียนได้แบบนี้

 

```
MAX Proseware Qty per Brand = MAXX(DISTINCT('Product'[Brand]),[Proseware Total Qty])
```

 

ดังนั้นการวิเคราะห์ การมองที่บรรทัดของ “**ตารางจำลอง**” ที่ Brand เป็น Litware จะเป็นแบบนี้

 

**แบ่งกลุ่ม**

 
1. Filter Context ดั้งเดิมของจุดที่สนใจ (รวมถึง Filter Context ของ CALCULATE ชั้นนอกด้วย)
    - Customer[Cust Continent]=”Asia”
2. Row Context ดั้งเดิม
    - ‘Product'[ฺBrand]=”Litware” (เกิดในตารางจำลอง)
3. เงื่อนไข Filter Argument (ที่จริงๆ คือตาราง) ที่ตั้งใจเขียนใน CALCULATE ตัวนั้นๆ 
    - ‘Product'[Brand]=”Proseware” (จาก CALCULATE ใน Measure)
4. CALCULATE Modifier เช่น REMOVEFILTERS, ALL, ALLSELECTED, ALLEXCEPT, USERELATIONSHIP, CROSSFILTER
    - ไม่มี

 

**ขั้นตอนการทำงาน**

 
- เก็บ **Filter Argument** เอาไว้ในใจ
    - ‘Product'[Brand]=”Proseware”
- Copy Filter Context เดิมมาเป็น New Filter Context
    - Customer[Cust Continent]=”Asia”
- Row Context
    - ‘Product'[ฺBrand]=”Litware“ => ถูกทำให้เป็นส่วนหนึ่งของใน New Filter Context
- CALCULATE Modifier
    - ไม่มี
- เอา **Filter Argument** ที่เก็บไว้มาใช้ สรุปแล้ว New Filter Context ได้แบบนี้
    - Customer[Cust Continent]=”Asia”
    - ‘Product'[Brand]=”Proseware” ซึ่งจะมาทับ Litware ไปเลยเนื่องจากอ้างอิงถึง Field เดียวกัน

 

ดังนั้นในตารางจำลองบรรทัดที่เป็น Litware จึงมีผล Filter จากทั้ง Brand Prosware และจากทวีป Asia นั่นเอง

 

#### ผลลัพธ์ของการใช้ Measure ทั้ง 3 จะเป็นแบบนี้

 ![7](https://www.thepexcel.com/wp-content/uploads/2022/09/calculate-pt2-007-1024x556.png) 

## การใช้ CALCULATETABLE

 

### ทำความรู้จัก CALCULATETABLE

 

CALCULATETABLE คือฟังก์ชันที่มีความสามารถเปลี่ยน Filter Context ได้ ซึ่งคล้าย CALCULATE มากๆ ต่างกันนิดเดียวคือ CALCULATETABLE ต้องให้ผลลัพธ์ออกมาเป็นตาราง แทนที่จะเป็นค่าค่าเดียวแบบที่ CALCULATE ทำได้

 

```
CALCULATETABLE ( <ตาราง> , [ Filter1 ] , [ Filter2],... )
```

 

ยกตัวอย่างเช่น ถ้าผมต้องการตาราง OrderDetail เฉพาะรายการที่มาจากลูกค้าบริษัท และมาจาก ร้านค้าทวีป Asia และซื้อสินค้าที่ราคาแพงกว่า 1000 บาท ผมสามารถเขียนแบบนี้ได้เลย

 

```
Person Order in Asia Store = 
CALCULATETABLE(OrderDetail,
    Customer[Customer Type]="Person",
    Store[Store Continent]="Asia",
    'Product'[Unit Price]>1000
    )
```

 

ซึ่งผลลัพธ์ที่ได้จะออกมาเป็นตาราง คล้ายกับการใช้ FILTER เลย แต่ว่าผมสามารถเขียนเงื่อนไขจาก Field ไหนก็ได้ใน Model โดยไม่ต้องใช้ RELATED เหมือนกรณีทำด้วย FILTER ด้วยซ้ำ

 ![8](https://www.thepexcel.com/wp-content/uploads/2022/09/calculate-pt2-008-1024x444.png) 

สำหรับข้อเสียของ CALCULATETABLE ก็เป็นเช่นเดียวกับ CALCULATE คือ การเขียนอ้างอิงเงื่อนไข จะต้องอ้างถึงคอลัมน์ที่มีตัวตนใน Data Model เท่านั้น เช่น ผมจะเขียนเงื่อนไขว่าเอาเฉพาะรายการที่อัตรากำไรเกิน 60% แบบนี้ไม่ได้ เพราะผมไม่ได้มีคอลัมน์นั้นจริงๆ (แต่ FILTER ทำได้)

 

การใช้ **CALCULATETABLE ปกติเค้าไม่ได้ใช้สร้างตารางที่มีตัวตนจริงๆ หรอกครับ** แต่เค้าใช้สร้างตารางจำลองใน Measure มากกว่า ซึ่งจะมีประโยชน์มากๆ เลย **และในเมื่อมันให้ผลลัพธ์เป็นตาราง มันจึงสามารถใช้เป็นเงื่อนไขของ CALCULATE ได้ด้วย!!**

 

จริงๆ แล้วเราก็สามารถใช้ Table Function อะไรก็ตามใน CALCULATETABLE ได้หมดเลย **รวมถึงเงื่อนไข Filter ของ CALCULATETABLE จริงๆ แล้วก็เป็นตารางเช่นเดียวกับ CALCULATE ด้วย**นั่นเอง ดังนั้นสูตรข้างบนสามารถเขียนได้แบบนี้ (เหมือนกรณี CALCULATE)

 

```
Person Order in Asia Store = 
CALCULATETABLE(OrderDetail,
    FILTER(ALL(Customer[Customer Type]),Customer[Customer Type]="Person"),
    FILTER(ALL(Store[Store Continent]),Store[Store Continent]="Asia"),
    FILTER(ALL('Product'[Unit Price]),'Product'[Unit Price]>1000)
    )
```

 

ซึ่ง Performance จะดีกว่าการ FILTER ตรงๆ ด้วย เนื่องจากมีการใช้ ALL ทำให้เห็นค่าไม่ซ้ำก่อนจะคิดเงื่อนไข ทำให่การ Scan ใช้เวลาน้อยลง

 

### จริงๆ แล้ว Time Intelligence นั้นแฝงไปด้วย CALCULATETABLE

 

ฟังก์ชัน Time Intelligence ถ้ามีการอ้างอิงถึงคอลัมน์ Date ใน Date Table จริงๆ นั้นแฝงไปด้วย CALCULATETABLE อยู่นะ เช่น

 

เช่น SAMEPERIODLASTYEAR ที่ดูง่ายๆ เหมือนไม่มีอะไรนั้น หาเขียนแบบนี้

 

```
SAMEPERIODLASTYEAR(dDate[Date])
```

 

จะหมายถึง

 

```
SAMEPERIODLASTYEAR( CALCULATETABLE ( DISTINCT (dDate[Date]) ) )
```

 

ซึ่งก็จะให้ผลลัพธ์เป็นตารางที่ย้อนเวลากลับไป 1 ปีนั่นเอง (และอาจมี Context Transition ได้)

 

## Case ซับซ้อน :

 

### อยากได้ยอดปีก่อนหน้า โดยสนใจวันประจำสัปดาห์ ด้วย CALCULATETABLE

 

สมมติว่าผมต้องการแสดงยอด Total Qty เฉพาะวันที่เลือกในแต่ละปีของ Brand ที่สนใจ แล้วผมเลือก Slicer วันจันทร์จะได้แบบนี้

 ![9](https://www.thepexcel.com/wp-content/uploads/2022/09/calculate-pt2-009-1024x611.png) 

ทีนี้ผมอยากเอาปีมาเป็น Slicer แล้วเลือกปี 2020 ไว้ จากนั้นผมอยากจะแสดงยอดของปีก่อนหน้าด้วย จึงใช้ SAMEPERIODLASTYEAR ทำ Time Intelligence ออกมา ได้แบบนี้ แล้วลองเอาไปใส่ในรายงานที่ต้องการ

 

```
Total Qty Prev Year = CALCULATE([Total Qty],SAMEPERIODLASTYEAR(dDate[Date]))
```

 

### ทำไม Time Intelligence ปกติถึงไม่ Work?

 

ปรากฏว่าเลขออกมาไม่ถูกต้อง!! เพราะเลข Prev Year ที่ได้ไม่เห็นตรงกับของปี 2019 ในตารางข้างบนเลย??

 ![10](https://www.thepexcel.com/wp-content/uploads/2022/09/calculate-pt2-010-1024x617.png) 

สาเหตุที่ไม่ตรง เป็นเพราะ **SAMEPERIODLASTYEAR เริ่มต้นจาก Filter Context ที่เป็นวันจันทร์ทั้งหมด แล้วย้อนเวลากลับไป 1 ปี ให้ลงเลขวันเดิม** ซึ่งอาจจะไม่ใช่วันจันทร์แล้วนั่นเอง ดังนั้นผลที่ได้จึงไม่ใช่วันจันทร์ของปี 2019

 

แล้วถ้าเราต้องการวันจันทร์ของปีก่อนหน้าด้วยจริงๆ เราจะทำยังไงล่ะ?

 

ผมจะลองใช้วิธีสร้างตารางวันที่ในปีปัจจุบันทั้งหมด แล้วย้อนเวลากลับไป 1 ปีจากนั้นค่อยเลือกให้เป็นวันจันทร์ โดยผมจะใช้วิธีสร้างตัวแปรมาเก็บค่าก่อน จะได้ไม่ซับซ้อนจนเกินไป แล้วลองใช้ COUNTROWS เช็คดูว่าผลลัพธ์เป็นอย่างไร

 

สมมติผมเขียนแบบนี้ ว่าให้ลองดูคอลัมน์วันที่แล้วนับจำนวนแถวออกมา

 

```
Total Qty Prev Year SelectedDay = 
VAR PrevYearDate =CALCULATETABLE(DISTINCT(dDate[Date]))
RETURN COUNTROWS(PrevYearDate)
```

 

ผลจะได้ออกมาเป็น 52 เพราะในปี 2020 ที่เลือกอยู่นั้นมีวันจันทร์ 52 วันนั่นเอง

 ![11](https://www.thepexcel.com/wp-content/uploads/2022/09/calculate-pt2-012.png) 

ทีนี้ผมต้องการจะทำการปลด Filter เรื่องวันออกไปซะ ก่อนจะทำการ DISTINCT วันออกมา ดังนั้นสามารถใช้ CALCULATETABLE เขียนแบบนี้ได้

 

```
Total Qty Prev Year SelectedDay = 
VAR PrevYearDate =CALCULATETABLE(DISTINCT(dDate[Date]),REMOVEFILTERS(dDate[DayName]))
RETURN COUNTROWS(PrevYearDate)
```

 

ผลลัพธ์จำนวนวันที่จะได้ 366 วัน นั่นคือจำนวนวันในปี 2020 นั่นเอง

 

ทีนี้ผมจะทำการย้อนเวลากลับไป 1 ปี ก็เลวอยากใช้ SAMEPERIODLASTYEAR หรือ DATEADD มาช่วย

 

แต่ผมจะสร้างตัวแปรใหม่แบบนี้ไม่ได้นะ เพราะ**ตัวแปรใน VAR มันจะถูกคำนวณทิ้งไว้จนกลายเป็นค่าคงที่แล้ว** ถึงจะเปลี่ยน Filter Context ก่อนคำนวณค่าคงที่ก็ไม่มีผลอะไร (มันจะได้ 366 วันซึ่งผิด)

 

```
Total Qty Prev Year SelectedDay = 
VAR PrevYearDate =CALCULATETABLE(DISTINCT(dDate[Date]),REMOVEFILTERS(dDate[DayName]))
VAR PrevYearDate2=CALCULATETABLE(PrevYearDate,SAMEPERIODLASTYEAR(dDate[Date]))
RETURN COUNTROWS(PrevYearDate2)
```

 

วิธีที่ถูกต้องคือต้องเขีนยสูตรที่สามารถเปลี่ยน Filter Context ตอนคิดตัวแปรแรกเลย

 

แต่จะเขียนแค่นี้ก็ไม่ Work อีก…

 

```
Total Qty Prev Year SelectedDay =
VAR PrevYearDate =
    CALCULATETABLE (
        DISTINCT ( dDate[Date] ),
        REMOVEFILTERS ( dDate[DayName] ),
        SAMEPERIODLASTYEAR ( dDate[Date] )
    )
RETURN
    COUNTROWS ( PrevYearDate )
```

 

สาเหตุที่ไม่ Work เราสามารถใช้วิธีการคิดของ CALCULATE มาพิจารณาได้ครับ เช่น

 

**แบ่งกลุ่ม**

 
1. Filter Context ดั้งเดิมของจุดที่สนใจ (รวมถึง Filter Context ของ CALCULATE ชั้นนอกด้วย)
    - dDate[Year]=2020
    - dDate[DayName]=”Monday”
2. Row Context ดั้งเดิม
    - ไม่มี
3. เงื่อนไข Filter Argument (ที่จริงๆ คือตาราง) ที่ตั้งใจเขียนใน CALCULATE ตัวนั้นๆ 
    - SAMEPERIODLASTYEAR ( dDate[Date] )
4. CALCULATE Modifier เช่น REMOVEFILTERS, ALL, ALLSELECTED, ALLEXCEPT, USERELATIONSHIP, CROSSFILTER
    - REMOVEFILTERS ( dDate[DayName] ) ที่เราตั้งใจเขียน
    - REMOVEFILTERS ( dDate ) ที่แถมมาจาก Time Intelligence เช่น SAMEPERIODLASTYEAR

 

**ขั้นตอนการทำงาน**

 
- เก็บ **Filter Argument** เอาไว้ในใจ
    - SAMEPERIODLASTYEAR ( dDate[Date] ) อันนี้มันจะมองวันจันทร์ปี 2020 ก่อน แล้วย้อนกลับไป 1 ปี ซึ่งจะไม่ใช่วันจันทร์ (ปัญหาเดิม)
- Copy Filter Context เดิมมาเป็น New Filter Context
    - dDate[Year]=2020
  
  
    - dDate[DayName]=”Monday”
- Row Context
    - ไม่มี
- CALCULATE Modifier
    - REMOVEFILTERS ( dDate[DayName] )
    - REMOVEFILTERS ( dDate )
    - สรุปแล้ว Filter ในตาราง Expanded ของ dDate หายหมด
- เอา **Filter Argument** ที่เก็บไว้มาใช้ สรุปแล้ว New Filter Context ได้แบบนี้
    - SAMEPERIODLASTYEAR ( dDate[Date] ) (ปัญหาเดิม)

 

นี่คือสาเหตุที่ไม่ Work ครับ เพราะเรายังติดปัญหาเดิมอยู่ดี Step การทำงานของมันไม่ได้ทำงานตามลำดับที่เราคาดหวังไว้ เพราะตัว SAMEPERIODLASTYEAR มันมาจำกัดวันที่ให้เห็นแค่วันจันทร์ ในขณะที่จริงๆ ผมอยากได้วันที่ของปีที่แล้วแบบไม่สนใจวันประจำสัปดาห์

 

### วิธีที่ Work

 

ถ้าเราเข้าใจลำดับการคำนวณเวลาใช้ CALCULATE ซ้อนกันดีแล้ว และเข้าใจพฤติกรรมของ VAR แล้ว เราจะสามารถกำหนดลำดับที่ถูกต้องได้แบบนี้

 

แม้การคิดสูตรใดๆ ก็ตามจะต้องคิดที่ตัวในสุดก่อน แต่ CALCULATE หรือ CALCULATETABLE ตัวในสุดมันจะมองสภาพแวดล้อมของ Filter Context ที่มันทำงานอยู่ (ซึ่งมีผลจาก CALCULATE/CALCULATETABLE ตัวนอกได้) **ดังนั้นสรุปแล้ว เวลาคิดเราจะต้องไล่เปลี่ยน Filter ของ CALCULATETABLE ชั้นนอกก่อน แล้วค่อยไล่เปลี่ยนชั้นใน** ดังนั้นเราสามารถเขียนแบบนี้ได้เลย

 

```
Total Qty Prev Year SelectedDay = 
VAR MonthList=DISTINCT(dDate[Month])      //Step1 เก็บค่าเดือน Context ปัจจุบันไว้เป็นค่าคงที่ 
VAR DayList=DISTINCT(dDate[DayName])      //Step1 เก็บค่าวัน Context ปัจจุบันไว้เป็นค่าคงที่
VAR PrevYearDate =
    CALCULATETABLE(
        CALCULATETABLE (
        CALCULATETABLE (
            DISTINCT ( dDate[Date] ),
            MonthList,                           //Step4 ทำให้เห็นเฉพาะวันที่เห็นใน Context
            DayList                              //Step4 ทำให้เห็นเฉพาะเดือนที่เห็นใน Context
        ),
        SAMEPERIODLASTYEAR ( dDate[Date] )),     //Step3 ย้อน 1 ปี เห็นวันที่ปีก่อนทั้งปีเสมอ
        ALLEXCEPT(dDate,dDate[Year])             //Step2 เห็นวันที่ในปีปัจจุบันทั้งปีเสมอ
    )
VAR Result= CALCULATE([Total Qty],PrevYearDate)  //Step5 เอาตารางวันที่ในปีก่อนหน้ามาคำนวณ
RETURN Result
```

 

แปลว่าขั้นตอนการคิดจะเป็นแบบนี้ นั่นคือไล่ Filter Context จากชั้นนอกไปใน

 
- ตัวแปร MonthList และ DayList เก็บค่าเป็นตาราง ณ Context แรกสุดเอาไว้ก่อนเป็นค่าคงที่ ไม่เปลี่ยนแปลง
- ALLEXCEPT ( dDate, dDate[Year] ) มันจะปลด Filter ทุกอย่างยกเว้นปี ทำให้เห็นเฉพาะปีปัจจุบัน แบบเต็มปีเสมอ
- SAMEPERIODLASTYEAR ( dDate[Date] ) ในเมื่อเดิมมองเห็นเต็มปีแล้ว ก็จะย้อนกลับไป 1 ปี แบบเต็มปีด้วย
- จากสั้นใส่ Filter ตาม MonthList และ DayList เข้าไป เพื่อให้เหลือช่วงเดือนและวันประจำสัปดาห์ที่ถูกต้อง
- แล้วค่อย List คอลัมนืวันที่ออกมาเป็นตารางด้วย DISTINCT ( dDate[Date] ) แล้วเก็บไว้ในตัวแปร PrevYearDate
- แล้วเอาตัวแปร PrevYearDate ไปใช้ใน CALCULATE เพื่อคำนวณ [Total Qty] ออกมาเป็นอันจบเลย

 

สุดท้ายเราจะได้ค่าที่ถูกต้องดังนี้ เหนื่อยเลย

 ![12](https://www.thepexcel.com/wp-content/uploads/2022/09/calculate-pt2-014-1024x506.png) 

อย่างไรก็ตาม วิธีคิดอาจมีหลายแบบ ถ้าใครลองวิธีไหนแล้วได้ผลลัพธ์ที่น่าสนใจ ลองเอามาแชร์กันได้ครับ

 

สำหรับบทความนี้ยาวและยากมากๆ แล้ว ผมขอจบเท่านี้ก่อน

 

## ตอนต่อไป

 

ตอนต่อไปจะเอาทุกอย่างมายำกันแบบสุดๆ แล้ว น่าจะเป็นตอนจบแล้วล่ะครับ

---

_Source: [https://www.thepexcel.com/calculate-dax-in-depth-pt2/](https://www.thepexcel.com/calculate-dax-in-depth-pt2/)_