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

ตอนนี้เป็นเนื้อหาที่ต่อจาก 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])

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

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

สาเหตุคือ 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 มาครอบให้โดยอัตโนมัติ!

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 เกิดขึ้นมาในแต่ละแถว
• จากนั้นหาค่ามากสุดออกมาเพราะเราใช้ MAXX (ตอนจบ หาค่ามากสุดของคอลัมน์จำลอง)

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

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

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

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

ซึ่งจริงๆ แล้วต่อให้เราอยากได้ในระดับ 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 นั่นเอง

แปลว่าคอลัมน์ใหม่ในตารางจำลองทั้งหมด ได้รับผล 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 จะเป็นแบบนี้

การใช้ 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 ด้วยซ้ำ

สำหรับข้อเสียของ 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 วันจันทร์จะได้แบบนี้

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

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

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

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

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

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

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

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

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

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

ทีนี้ผมต้องการจะทำการปลด 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] ออกมาเป็นอันจบเลย

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

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

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

ตอนต่อไป

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