Power BI ตอนที่ 20 : การคำนวณต้นทุนแบบ FIFO ด้วย DAX

ตอนนี้จะเป็นเนื้อหาที่ประยุกต์ใช้ความสามารถของ DAX ที่เราเรียนมา ในการคำนวณสิ่งที่ค่อนข้างซับซ้อน นั่นก็คือ ต้นทุนแบบ FIFO นั่นเองครับ ซึ่งวิธีในบทความนี้จะสามารถทำให้คุณสามารถคำนวณสิ่งนี้ได้แบบอัตโนมัติ ไม่ต้องทน Manual อีกต่อไป 555 แต่ก่อนอื่นเรามาทบทวนหลักการของ FIFO กันนิดนึงก่อนจะไปดูวิธีทำใน DAX

( Edit 30/6/2020 14:20 : ผมมีแก้ Code ที่ Error เรื่องเครื่องหมาย & ให้แล้วนะครับ รบกวนลอง refresh หน้าเว็บด้วย)

FIFO คืออะไร?

FIFO หรือ First In, First Out คือวิธีการทางบัญชี ที่จะใช้ต้นทุนสินค้าชิ้นที่ซื้อเอาไว้เก่าที่สุดก่อนแล้วค่อยไล่ไปชิ้นใหม่ขึ้นเรื่อยๆ ซึ่งผลของมันจะเห็นได้ชัดเลยกับสินค้าที่มีราคาซื้อเปลี่ยนแปลงไปเยอะๆ

เช่น เราซื้อสินค้าไป 3 รอบ จากเก่าไปใหม่ คือ

• 2 ชิ้น @ 4บาท/ชิ้น
• 5 ชิ้น @ 7 บาท/ชิ้น
• 3 ชิ้น @5 บาท/ชิ้น

ถามว่าถ้าเราขายสินค้าไป 2 รอบ คือ

• ขายรอบแรก : 4 ชิ้น @ 6 บาท/ชิ้น
• ขายรอบสอง : 5 ชิ้น @ 8 บาท/ชิ้น

แต่ละรอบจะได้กำไรเท่าไหร่?

การขายรอบแรก 4 ชิ้น

• ต้นทุนจะใช้ของสินค้า 2 ชิ้น @ 4บาท/ชิ้น + 2 ชิ้น @ 7 บาท/ชิ้น (เพราะขายทั้งหมดแค่ 4) = 8+14 = 22 บาท
• แปลว่ากำไร 4*6 – 22 = 2 บาท

การขายรอบสอง 5 ชิ้น

• ต้นทุนจะใช้ของสินค้า 3 ชิ้น @ 7บาท/ชิ้น (เพราะราคา 7 บาทเหลือแค่ 3 ชิ้น) + 2 ชิ้น @ 5 บาท/ชิ้น = 21+10 = 31 บาท
• แปลว่ากำไร 5*8 – 31 = 9 บาท

จะเห็นว่าวิธีการคำนวณนั้นยุ่งยากมาก เพราะต้องนั่งไล่ดูว่าใช้สินค้าเดิมไปถึงตัวไหนแล้ว จะได้เอาราคามาถูกอัน และถ้าสินค้ามีหลายชนิดอีก ยิ่งยากเข้าไปใหญ่เลย!!

เดี๋ยวทั้งหมดนี้เราจะมาทำด้วย Power BI กันนะครับ แต่สำหรับคนที่อยากใช้ Excel สามารถไปดูที่ Link เหล่านี้ได้

วิธีคำนวณ FIFO ด้วย Excel

มีหลายท่านที่ได้ทำวิธีการคำนวณ FICO ด้วย Excel ไปแล้ว ผมจึงไม่ขอทำซ้ำอีก แต่ละแบบก็มีข้อดีข้อเสียต่างกันไป

• QQ15 Calculate FIFO cost (สูตรโดย Excel Wizard) : อันนี้เป็นวิธีใช้สูตรกรณีเป็นสินค้าเดียว
• วิธีใช้สูตรแบบสินค้าหลายตัว (สูตรโดย Excel Wizard) เป็น post ในกลุ่ม Excel Super Fan ที่ถามมาโดยคุณ Nattaporn Chamwong : สามารถคำนวณได้จากสินค้าหลายตัว และดูผลลัพธ์พร้อมกันได้ทุกตัว [แต่บอกไว้ก่อนว่าซับซ้อนมากๆ]
• FIFO Costing with Excel Data Table using inward and outward table (อ. สมเกียรติ ฟุ้งเกียรติ แห่ง Excel Expert Training) : ใช้ concept ของ Data Table คำนวณได้จากสินค้าหลายตัว แต่ดูผลลัพธ์ได้ทีละตัว [วิธีนี้ผมชอบตรง concept ไม่ซับซ้อนดี และตรวจสอบผลลัพธ์ได้ง่าย]

วิธีคำนวณ FIFO ด้วย DAX ของผม

สำหรับวิธี DAX ของผม จะสามารถคำนวณได้จากสินค้าหลายตัว และแสดงผลลัพธ์ได้พร้อมกันทั้งหมด แถมเอาไปหมุนดูในมุมไหนก็ได้ (เพราะเอาไปสร้าง DAX Measure ได้) ดังนั้นมันจะยืดหยุ่นสุดๆ ไปเลยครับ โดยที่ผมประยุกต์เอา concept แนวคิด วิธีตาราง Data Table ของ อ. สมเกียรติ มาดัดแปลงให้ดีขึ้น โดยใช้ความสามารถของเครื่องมือยุคใหม่อย่าง DAX

ซึ่งผมใช้ concept ของ การสร้างตารางจำลองด้วย FILTER ผสมผสานกับการใช้ Iterator อย่าง SUMX ทำให้สามารถสร้างตารางจำลองในอากาศได้เลย ทำให้มันเป็นวิธีที่ทรงพลังมากๆ

ถ้าใครงงกับคำศัพท์เหล่านี้ก็ไปอ่านบทเก่าๆ เรื่อง SUMX กับ FILTER ได้นะครับ ส่วนใครไม่เคยเห็น VAR กับ RETURN ก็ไปอ่านได้ที่นี่

ดังนั้นเพื่อเป็นการให้เกียรติ อ.สมเกียรติ ผู้คิด Logic ในไฟล์เดิม และเพื่อให้ทุกคนสามารถเห็นภาพได้ชัด ผมจะใช้ Data เดียวกับของ อ.สมเกียรติ เลย แต่มีการแก้หัวตารางให้อ่านง่าย และเพิ่มราคาขายเข้าไป จะได้คิดกำไรไปได้เลย ดังนั้นทุกคนจะสามารถลองเปลี่ยนค่าในไฟล์ Excel เล่นตามได้ เพื่อให้เข้าใจว่าแต่ละขั้นตอนมันเกิดอะไรขึ้นบ้าง

สรุปแล้ว Data มีดังนี้

ก่อนอื่นผมเอาตารางการซื้อขายเข้าสู่ Power BI ก่อนแบบ Get Data จาก Excel ที่ผมเตรียมไว้ต่างหากอย่างตรงไปตรงมา ดังนี้

เอาล่ะ เรามาเริ่มเขียน DAX เพื่อสร้างคอลัมน์ใหม่ขึ้นมาช่วยคำนวณกันได้เลย (ผมสร้างเป็นคอลัมน์ใหม่ เพื่อให้ทุกคนเห็นภาพชัดเจนขึ้น ซึ่งจริงๆ บางขั้นตอนไม่ต้องออกมาเป็นคอลัมน์ใหม่ก็ได้นะ)

สิ่งแรกเพื่อให้การคำนวณ FIFO ง่ายขึ้น จะต้องมีจำนวน Unit ที่ซื้อ และขาย แบบสะสมซะก่อน ซึ่งสามารถใช้คำสั่ง SUMX + FILTER มาช่วย จะเห็นว่า DAX ใช้การอ้างอิงแบบ Cell Reference ของ Excel ไม่ได้ การเขียนเพื่ออ้างบรรทัดก่อนหน้าแบบสะสมก็จะยากกว่า Excel พอสมควรเนอะ

กด New Column แล้วใส่สูตรของแต่ละตัว โดยที่ผมมี comment อธิบายการทำงานให้ในสูตรด้วย // นะครับ

AccumBuy = ยอดซื้อสะสม

AccumBuy = 
VAR CurrentProductID = TXData[ProductID]
VAR CurrentNum = TXData[Num]
//ใช้ VAR เก็บค่าของข้อมูล ProductID กับ Num ของตาราง TXData 
//เอาไว้ใช้อ้างอิงอีกทีภายใน SUMX (ซึ่งมีการเปลี่ยนตาราง)
RETURN
// ใช้ SUMX เพื่อรวมค่าจากตารางจำลองที่สร้างขึ้นมา
// ใช้ Filter สร้างตารางในแถวก่อนหน้าทั้งหมดเฉพาะ ProductID ปัจจุบัน
    SUMX (
        FILTER (
            TXData,
            TXData[ProductID] = CurrentProductID
                && TXData[Num] <= CurrentNum
        ),  
        TXData[UnitBuy]
    ) 

AccumSell = ยอดขายสะสม

AccumSell = 
// หลักการเดียวกับ AccumBuy แค่เปลี่ยนเป็นการขาย
VAR CurrentProductID = TXData[ProductID]
VAR CurrentNum = TXData[Num]
RETURN
    SUMX (
        FILTER (
            TXData,
            TXData[ProductID] = CurrentProductID
                && TXData[Num] <= CurrentNum
        ),
        TXData[UnitSell]
    )

แค่มี 2 ตัวนี้ จริงๆ เราก็สามารถคำนวณจำนวน Unit Balance ณ แต่ละขณะได้แล้ว ดังนี้

UnitBalance = TXData[AccumBuy]-TXData[AccumSell]

สรุปได้ข้อมูลทั้ง 3 คอลัมน์ดังนี้

Concept การคำนวณเรื่อง FIFO ในไฟล์ของ อ. สมเกียรติ

ทีนี้ Concept การคำนวณเรื่อง FIFO ในไฟล์ของ อ. สมเกียรติ คือ เอาจำนวนขายที่มีค่อยๆ มาหักลบจำนวนซื้อไล่จากเก่าไปใหม่ โดยเทียบเอาตัวต่ำกว่าระหว่างจำนวนซื้อแต่ละครั้งนั้นๆ กับจำนวนขายที่ยังค้างอยู่ ซึ่งจะได้จำนวนขายที่มาจับคู่กับจำนวนซื้อจริงๆ

เช่น ในรูปนี้ จะเห็นว่ามีจำนวนขาย 150,000 ชิ้น มันก็จะค่อยๆ เอามาหักลบ 50,000 10,000 70,000 และสุดท้ายเหลือ จับคู่กับตัวที่ซื้อ 100,000 ชิ้นแค่ 20,000 เท่านั้น

จากนั้นก็เอาจำนวนชิ้นที่คำนวณได้ไปคูณ UnitCost เพื่อให้ออกมาเป็น Cost of Goods Sold (ผมจะเรียกย่อๆ ว่า COGS) ของการซื้อแต่ละครั้งที่จับคู่กับจำนวนขายจริง (ในช่อง K6:K24)

จากนั้นเอา COGS ของการขายทุกครั้งมารวมกัน ได้ช่อง K25 ตัวล่างสุดของตารางกลาง

ทีนี้เอาค่า K25 ที่ได้จากตารางกลางไปโปรยในตารางขวาในคอลัมน์ Q ด้วย Data Table ในฐานะของ Cost of Goods Sold รวมของแต่ละ Transaction ที่มีการขายจริง

เช่น

• ขายสะสม 30,000 ชิ้น มี COGS 300,000
• ขายสะสม 70,000 ชิ้น มี COGS 750,000
• แปลว่าที่ขายเพิ่มมา 40,000 ชิ้น ต้องมี COGS 750,000-300,000 คือ 450,000 นั่นเอง

นี่คือ Concept วิธีคิด FIFO ของ อ. สมเกียรติ ซึ่งคุณควรทำความเข้าใจ Concept นี้ใน Excel ให้เข้าใจก่อน จึงจะสามารถเข้าใจสิ่งที่ผมจะทำด้วย DAX แทนได้ครับ ใครอยากอ่านวิธีคิดแบบละเอียดไปดูได้ที่นี่

คำนวณด้วย DAX (ตรงนี้ค่อนข้างซับซ้อนเลยล่ะ)

ทีนี้เพื่อให้คำนวณ COGS สะสมได้ เพื่อให้ง่าย เราจะคำนวณจำนวนซื้อสะสมในบรรทัดก่อนหน้าซะก่อน ดังนี้

PrevAccumBuy = 
VAR CurrentProductID = TXData[ProductID]
VAR CurrentNum = TXData[Num]
// เอาค่า AccumBuy ใน Transaction ก่อนหน้า เฉพาะของ ProductID ปัจจุบันมา
// ใช้ MAXX เพื่อเอาค่ามากสุดจากตารางจำลองที่สร้างขึ้นมา (ก็จะได้ตัวสะสมอันล่าสุด)
// ใช้ Filter สร้างตารางในแถวก่อนหน้าทั้งหมดเฉพาะ ProductID ปัจจุบัน
VAR MyResult =
    MAXX (
        FILTER (
            TXData,
            TXData[Num] < CurrentNum
                && TXData[ProductID] = CurrentProductID
        ),
        TXData[AccumBuy]
    )
RETURN
// ให้แสดงค่าเฉพาะบรรทัดที่เป็นการซื้อเท่านั้น นอกนั้นเป็น 0 ไป
    IF ( TXData[UnitBuy] > 0, MyResult ) + 0

จะได้ตารางหน้าตาแบบนี้ ซึ่งผมจะเอา PrevAccumBuy นี้ไว้ใช้หักลบออกจาก AccumSell เพื่อที่จะได้รู้ว่า ยังมีจำนวนขายให้มาเทียบกับจำนวนซื้อได้อีกกี่ตัว

ซึ่ง PrevAccumBuy ของผมจะคล้ายกับส่วนนี้ในสูตรใน Excel เลย (สมมติดูในช่อง J10)

IF(AND(@Id=IdKey,@Date>=From,E10>0),MIN(E10,TotalUnit-SUM($J$5:J9)),0)

ทีนี้เราจะสามารถใช้สูตรนี้เพื่อคำนวณ COGS สะสมได้

CostFIFOAccum = 
VAR CurrentNum = TXData[Num]
VAR CurrentProductID = TXData[ProductID]
VAR AccumSell = TXData[AccumSell]
VAR PrevAccumBuy = TXData[PrevAccumBuy]
VAR FIFOBuytable =
//สร้างตารางจำลอง โดยคัดมาแต่รายการก่อนหน้าที่เป็นการซื้อเท่านั้น
    FILTER (
        TXData,
        TXData[UnitBuy] > 0
            && TXData[Num] < CurrentNum
            && TXData[ProductID] = CurrentProductID
    )
VAR FIFOBuytableAdd =
// สร้างคอลัมน์ "ActualDeduct" เพิ่มใน FIFOBuytable เพื่อให้รู้ว่าแต่ละจำนวนซื้อนั้นๆ มีจำนวนขายให้หักจริงเท่าไหร่
// เพื่อสร้างคอลัมน์ J ในตารางกลางของไฟล์ Excel
    ADDCOLUMNS (
        FIFOBuytable,
        "ActualDeduct", MIN ( TXData[UnitBuy], MAX ( AccumSell - TXData[PrevAccumBuy], 0 ) )
    )
VAR CalCost =
// เอาจำนวน unit ที่หักจริงสำหรับ Transaction นั้นๆ * ราคาซื้อ แล้ว SUM (ตรงนี้จะเหมือนคอลัมน์ K ในตารางกลางของไฟล์ Excel)
    SUMX ( FIFOBuytableAdd, [ActualDeduct] * TXData[UnitPrice] )
VAR CostFIFOAccum =
// เอาค่า COGS สะสมมาแสดงเฉพาะรายการที่เป็นการขาย ตรงนี้จะเหมือนคอลัมน์ Q ในตารางขวาของไฟล์ Excel
    IF ( TXData[UnitSell] > 0, CalCost )
RETURN
    CostFIFOAccum

จะเห้นว่าส่วนของ “ActualDeduct” ที่มีสูตร

MIN ( TXData[UnitBuy], MAX ( AccumSell - TXData[PrevAccumBuy], 0 ) )

นั้นจะใช้หลักการของสูตรใน Excel อันนี้เลย

IF(AND(@Id=IdKey,@Date>=From,E10>0),MIN(E10,TotalUnit-SUM($J$5:J9)),0)

ตอนนี้ผมก็ได้คอลัมน์ Q ของ ไฟล์ Excel แล้ว แต่ของผมดีกว่าตรงที่ออกมาพร้อมกันทุก Product เลย!!

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

CostFIFO = 
VAR CurrentNum = TXData[Num]
VAR CurrentProductID = TXData[ProductID]
VAR PrevFICOAccum =
// คำนวณยอด COGS สะสมของ Transaction ก่อนหน้า เฉพาะของ ProductID ปัจจุบัน
    MAXX (
        FILTER (
            TXData,
            TXData[Num] < CurrentNum
                && TXData[ProductID] = CurrentProductID
        ),
        TXData[CostFIFOAccum]
    )
VAR CurrentFIFOCost = TXData[CostFIFOAccum] - PrevFICOAccum
// เอาที่สะสมปัจจุบัน - สะสมก่อนหน้า = ได้ยอด COGS ปัจจุบัน
RETURN
// ให้แสดงค่า COGS เฉพาะรายการที่เป็นการขายเท่านั้น
    IF ( TXData[UnitSell] > 0, CurrentFIFOCost )

เท่านี้ผมก็ได้คอลัมน์ R ของไฟล์ Excel แล้ว (แต่ของผมออกมาพร้อมกันทุก Product เลยเช่นเคย)

หลังจากนี้ง่ายมากๆ แล้ว

หลังจากนี้ก็คำนวณ Revenue กับ Profit ได้ง่ายๆ ดังนี้

Revenue = TXData[UnitPrice]*TXData[UnitSell]

ProfitFIFO = TXData[Revenue]-TXData[CostFIFO]

พอได้พวกนี้ครบแล้ว ก็สามารถสร้าง DAX Measure ที่ต้องการเช่น TotalUnitBuy, TotalUnitSell, ToTotalRevenue, TotalCost, TotalProfit ได้แบบง่ายๆ ด้วย SUM ได้เลย

TotalUnitBuy = SUM(TXData[UnitBuy])

TotalUnitSell = SUM(TXData[UnitSell])

TotalRevenue = SUM(TXData[Revenue])

TotalCost = SUM(TXData[CostFIFO])

TotalProfit = SUM(TXData[ProfitFIFO])

จากนั้นเราก็สามารถเอาไปสร้าง Report ยังไงก็ได้แล้วล่ะ ตรงนี้ไม่มีอะไรยากแล้ว อยากดูรวมทุก Product หรือดูทีละอัน Power BI ทำได้หมด และทำให้ Interactive กันได้ด้วย

จบแล้ว

ใครทำตามแล้วสงสัยอะไรตรงไหนก็สามารถ comment ถามได้นะครับ บอกเลยว่าวิธีนี้มันทรงพลังมากๆ เพราะเราอยากดูมุมไหน อยาก Filter อะไรก็สามารถทำได้หมด

สารบัญ Series Power BI

• POWER BI ตอนที่ 01: POWER BI คืออะไร?
• POWER BI ตอนที่ 02: พื้นฐาน EXCEL ที่สำคัญก่อนจะเรียนรู้ POWER BI
• POWER BI ตอนที่ 03: ภาพรวมการใช้งาน POWER BI DESKTOP
• POWER BI ตอนที่ 04: สร้าง REPORT แรก ใน POWER BI
• POWER BI ตอนที่ 05: วิธีการ DRILL เพื่อเจาะลึกข้อมูลใน REPORT
• POWER BI ตอนที่ 06: การปรับแต่งสีใน VISUAL ด้วย CONDITIONAL FORMAT
• POWER BI ตอนที่ 07: เริ่ม GET DATA ตั้งแต่ไฟล์ยังว่างเปล่า
• POWER BI ตอนที่ 08: สร้าง DATA MODEL ที่เหมาะสม
• POWER BI ตอนที่ 09: สร้าง DATE TABLE ด้วย DAX
• POWER BI ตอนที่ 10: เรียนรู้ DAX เบื้องต้น
• POWER BI ตอนที่ 11: เรียนรู้ DAX Table Function – FILTER
• POWER BI ตอนที่ 12: DISTINCT, VALUES, ALL และผองเพื่อน
• POWER BI ตอนที่ 13: CALCULATE ฟังก์ชันที่ทรงพลังที่สุดใน DAX
• Power BI ตอนที่ 14: Context Transition และ พลังแฝงใน Measure
• Power BI ตอนที่ 15: วิธีดึงค่าจาก Slicer มาคำนวณใน Report
• Power BI ตอนที่ 16 : เดินทางข้ามเวลาไปกับ Time Intelligence DAX Function
• Power BI ตอนที่ 17 : วิธีทำรายงานเทียบเป้าหมาย Target vs Actual
• Power BI ตอนที่ 18 : วิธีการกระจายเป้า Allocate Target ด้วย DAX
• Power BI ตอนที่ 19 : การปรับ Cross Filter Direction เพื่อคำนวณค่าในตาราง Dimension
• ส่วนเสริม
• การคำนวณต้นทุนแบบ FIFO ด้วย DAX
• แสดงข้อมูลสรุปแบบ Top N + Others (ฉบับเทพเอ็กเซล)
• การวิเคราะห์ Event ที่มีช่วงเวลาเริ่มต้นกับสิ้นสุดคนละวัน
• เปรียบเทียบ MAX vs LASTDATE ในภาษา DAX

ใครสนใจอยากเรียนเป็นคลิปวีดีโอ ผมมีคอร์สออนไลน์ที่สอน Power BI ตั้งแต่พื้นฐาน สามารถไปดูรายละเอียดได้ที่นี่