คำนวณวันทำงานระหว่างวันที่สองวันใน LuckyTemplates

ฉันจะแสดงวิธีคำนวณวันทำงาน/วันทำงานระหว่างสองวัน โดยนับวันหยุดหรือไม่นับวันหยุด นี่คือหัวข้อที่ถูกถามซ้ำแล้วซ้ำเล่าใน ฟอ รัม

ฉันยังเชื่อด้วยว่านี่จะเป็นวิธีที่ดีในการปิดซีรีส์ Time Intelligence นี้ เพราะรู้ว่ามันครอบคลุมองค์ประกอบอื่นๆ มากมายที่กล่าวถึงก่อนหน้านี้ องค์ประกอบเหล่านี้รวมถึงการสร้างตารางวันหยุด การสร้างตารางวันที่แบบขยาย และการใช้ ฟังก์ชัน เป็นต้น คุณสามารถดูวิดีโอทั้งหมดของบทช่วยสอนนี้ได้ที่ด้านล่างของบล็อกนี้

สารบัญ

• วิธีคำนวณวันทำงานระหว่างสองวัน
• การคำนวณวันทำงานระหว่างวันที่โดยใช้ DAX
• การคำนวณวันทำงานโดยใช้ Power Query
• บทสรุป

วิธีคำนวณวันทำงานระหว่างสองวัน

ฉันจะพูดถึงวิธีการต่างๆ ที่คุณสามารถใช้เพื่อคำนวณวันทำงาน

Excel ช่วยให้ผู้ใช้สามารถคำนวณวันทำงานได้อย่างตรงไปตรงมาผ่านการใช้ฟังก์ชันNETWORKDAYS อย่างไรก็ตามDAX ไม่มีฟังก์ชันเช่นนั้นดังนั้นฉันจะหารือเกี่ยวกับวิธีแก้ไขปัญหานี้

วิธีหนึ่งในการทำเช่นนี้คือการใช้องค์ประกอบบางอย่างจากตารางวันที่ขยาย

อีกทางเลือกหนึ่งคือใช้วิธีที่ฉันได้รับจาก Imke Feldmann จากบล็อก thebiccountant.com เธอสร้างฟังก์ชัน NetworkDays แบบกำหนดเองที่สามารถเรียกใช้ได้โดยตรงจาก Power Queryทำให้กระบวนการเร็วขึ้นและซับซ้อนน้อยลงมาก

เพื่อแสดงให้คุณเห็นว่าวิธีการเหล่านี้ทำงานอย่างไร ฉันจะใช้สถานการณ์ทั่วไป

ฉันมีหนึ่งร้อยโครงการที่ฉันไม่เปิดเผยชื่อ

ฉันยังมีวันที่เริ่มต้นและวันที่สิ้นสุดสำหรับแต่ละโครงการ

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

โมเดลข้อมูลนั้นง่ายมาก มันมีตารางสำหรับวันที่และโครงการ

นอกจากนี้ยังแสดงตารางข้อเท็จจริง ซึ่งรวมถึงรหัสโครงการวันที่เริ่มต้นและวันที่สิ้นสุด

เพื่อวัตถุประสงค์ในการเปรียบเทียบ ฉันยังคำนวณ NetworkDays (ที่มีและไม่มีวันหยุด) โดยใช้ Excel (ไม่ใช่ power bi)

แน่นอน คุณสามารถทำสิ่งเดียวกันและนำข้อมูลจาก Excel เข้ามาได้ แต่เป้าหมายที่นี่คือการใช้ LuckyTemplates เพื่อคำนวณจำนวนวันทำงานโดยไม่ต้องใช้ Excel

การคำนวณวันทำงานระหว่างวันที่โดยใช้ DAX

เมื่อใดก็ตามที่คุณจัดการกับวันที่นับ การใช้ ฟังก์ชัน ในตาราง Dates จะมีประสิทธิภาพเสมอ

ฉันยังใช้DATESBETWEEN เพื่อจำกัดฟิลด์ระหว่างวันที่เริ่มต้นและวันที่สิ้นสุดให้แคบลง ฉันยังได้เพิ่มIsWorkingDayเพื่อใช้ตรรกะบูลีนง่ายๆ ว่าถ้าเป็นวันทำงานก็ควรจะเป็นวัน หยุดสุดสัปดาห์จะส่งกลับเป็น

ฉันจบมันด้วย ตัวกรอง เพื่อพิจารณาบริบทของตัวแบ่งส่วนข้อมูล

สำหรับเวอร์ชันที่มีวันหยุด มันคล้ายกับมาตรการที่ฉันเพิ่งพูดถึงไปมาก

เป็นนิพจน์เดียวกันโดยใช้ฟังก์ชันDATESBETWEEN ฉันเพิ่มและIsHoliday เท่านั้น

อีกครั้ง ฉันใช้ ฟังก์ชัน ALLSELECTEDในตอนท้ายสุด

ดูว่าจะเกิดอะไรขึ้นเมื่อฉันลากการวัดทั้งสองนี้ลงในตารางของฉัน

ตามที่คาดไว้ วัน ทำงานที่มีวันหยุดตรงกับคอลัมน์ Excel ที่มีวันหยุดอย่างสมบูรณ์แบบ สิ่งเดียวกันนี้ใช้กับคอลัมน์วันทำงานไม่มีวันหยุด ซึ่งตรงกับคอลัมน์ Excel ไม่มีวันหยุด

การคำนวณวันทำงานโดยใช้ Power Query

แนวทางที่ Imke Feldmann จากบล็อก thebiccountant.com พูดถึงทำให้มีวิธีแก้ปัญหาที่ง่ายยิ่งขึ้นเมื่อใช้ Power Query

ในบล็อกของเธอเกี่ยวกับฟังก์ชัน NETWORKDAYS เธอให้รหัส M ที่สามารถเรียกใช้ใน Power Query

เพียงคัดลอกรหัส M จากบล็อกของเธอ

จากนั้น ไปที่Power Queryและภายใต้New Sourceให้เปิดBlank Query

ไปที่ ตัว แก้ไขขั้นสูง

จากนั้นวางโค้ดทั้งหมดลงในหน้า ตรวจสอบให้แน่ใจว่าคุณมีเครื่องหมายถูกสีเขียวเพื่อให้แน่ใจว่าไม่มีข้อผิดพลาดในรหัสของคุณ

เมื่อเรียกใช้แล้ว ระบบจะนำคุณไปยังเทมเพลตนี้

ไม่สำคัญว่าฉันจะป้อนอะไรที่นี่ ดังนั้นฉันจะใช้วันที่ 1 มกราคม 2018 ถึง 1 มกราคม 2020

ตอนนี้จะปรากฏใต้ข้อความค้นหาของฉัน ฉันจะเปลี่ยนชื่อเพื่อให้ง่ายต่อการใช้งานในภายหลัง

ฉันจะตั้งชื่อมันว่าNetworkdays

ตอนนี้ฉันสามารถเริ่มใช้แบบสอบถามนั้นได้ ฉันจะเข้าไปที่Data ของ ฉัน

จากนั้นภายใต้Add Columnฉันจะคลิกที่Invoke Custom Function

ฉันจะเรียกNWD นี้ และใช้ฟังก์ชันNetworkdays

จากนั้น ฉันจะเลือกวันที่เริ่มต้นและวันที่สิ้นสุดจากเมนูแบบเลื่อนลงด้านล่าง

มันทำให้ฉันมีตัวเลือกที่นี่เพื่อเปลี่ยนวันเริ่มต้นสัปดาห์จากวันจันทร์เป็นอย่างอื่น

เพื่อจุดประสงค์นี้ วันจันทร์ไม่เป็นไร ฉันจะปล่อยไว้อย่างนั้น จากนั้นฉันจะคลิกตกลง

อย่างที่คุณเห็น ระบบจะคำนวณNetworkDaysที่เทียบเท่ากับคอลัมน์Excel NetworkDays ทันที

ตอนนี้ ขอผมปรับแต่งสิ่งนี้ เพื่อที่ผมจะได้ยกเว้นวันหยุด ฉันแค่ต้องไปที่ตารางวันหยุด ของฉัน

หากคุณจำได้จากเทมเพลต ตารางวันหยุดคือพารามิเตอร์ตัวที่สามที่นี่

ดังนั้นฉันจะใช้ตารางวันหยุดที่นี่และอ้างอิงคอลัมน์วันที่

ฉันจะคลิกเครื่องหมายถูกที่นี่เพื่อใช้การเปลี่ยนแปลงเหล่านั้น

[00:11:00]
ตอนนี้อัปเดตและลบวันหยุดทั้งหมดแล้ว เหมือนกันทุกประการกับคอลัมน์สำหรับ Excel NetworkDaysที่ไม่มีวันหยุด

ฉันจะเปลี่ยนชื่อคอลัมน์นี้เป็น NWD ไม่มีวันหยุด

การเพิ่มตัวเลขวันทำงานและวันหยุดสุดสัปดาห์ลงในตารางวันที่ของคุณ: เทคนิค Time Intelligence ใน LuckyTemplates
การแสดงตัวเลขวันทำงานใน LuckyTemplates โดยใช้ DAX
การคำนวณค่าเฉลี่ยใน LuckyTemplates: การแยกผลลัพธ์ของวันทำงานหรือวันหยุดสุดสัปดาห์โดยใช้ DAX

บทสรุป

คุณจะเห็นว่าทั้งสองวิธีที่ฉันใช้แตกต่างกันมาก แต่ทั้งสองวิธีให้ผลลัพธ์ที่เหมือนกัน สิ่งนี้แสดงให้เห็นว่ามีวิธีมากมายในการเพิ่ม LuckyTemplates ให้สูงสุด แม้ว่าฟังก์ชัน Excel เฉพาะที่คุณกำลังมองหาจะไม่มีคู่เทียบโดยตรงก็ตาม

นี่เป็นผลงานที่ยอดเยี่ยมสำหรับซีรี่ส์ Time Intelligence ฉันสนุกกับการทำงานกับ Melissa Dekorte เพื่อแสดงสถานการณ์ Time Intelligence ที่ยอดเยี่ยมทั้งหมดเหล่านี้

สิ่งหนึ่งที่ฉันต้องการเน้นจริงๆ ที่นี่คือ DAX เป็นส่วนที่น่าสนใจที่สุดของ LuckyTemplates แต่เมื่อคุณเชี่ยวชาญใน LuckyTemplates คุณจะเริ่มชื่นชมโซลูชันอื่นๆ ที่ไม่เกี่ยวข้องกับ DAX เลย วิธีการล่าสุดที่ฉันแสดงให้คุณเห็นข้างต้น ตลอดจนเคล็ดลับที่ฉันแบ่งปันในวิดีโอที่ผ่านมาบน Time Intelligence พิสูจน์ข้อเท็จจริงนี้

สิ่งที่ดีที่สุด