เคล็ดลับ LuckyTemplates: วิธีเปรียบเทียบ N วันทำการแรก

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

TJ Henneman ต้องการเปรียบเทียบวันที่เรียกเก็บเงิน 5 วันแรกของเดือนปัจจุบันกับวันที่เรียกเก็บเงิน 5 วันแรกของเดือนก่อนหน้า จากนั้นในวันที่ 10 วันที่ 15 และวันที่ 20 โปรดทราบว่าเขาต้องการดูเฉพาะวันที่เรียกเก็บเงินเท่านั้น ดังนั้นจึงไม่รวมวันหยุดสุดสัปดาห์และวันหยุดนักขัตฤกษ์ คุณสามารถตรวจสอบโพสต์ของเขาได้

ฉันคิดว่ามีวิธีแก้ปัญหา Power Query ที่น่าสนใจจริงๆ ที่นี่ ฉันยังทำงานร่วมกับโซลูชัน DAX ที่น่าสนใจ สำหรับปัญหานี้ ฉันจะพูดถึงบทหลังในบทช่วยสอนอื่น

ฉันยังต้องการแนะนำคุณเกี่ยวกับเทคนิคการรายงานล่าสุดที่ฉันใช้สำหรับ LuckyTemplates Challenge #16 ฉันใช้ตัวบ่งชี้KPIแบบเลื่อนซึ่งจะทำงานได้ดีสำหรับปัญหานี้โดยเฉพาะ

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

สารบัญ

• โมเดลข้อมูลของเรา
• การสร้างตารางซ้ำ
• การรวมตารางวันที่
• การสร้างคอลัมน์แบบกำหนดเอง
• การสร้างพารามิเตอร์แบบ What-If
• การสร้างหน่วยวัดสำหรับจำนวนวันทำการ
• บทสรุป

โมเดลข้อมูลของเรา

โมเดลข้อมูลของเราใช้ตารางวันที่ขยายบวกกับชุดตารางมิติข้อมูล เรายังมีตารางการขายที่มีข้อมูลบางส่วนสามปีในปีแรก ข้อมูลทั้งหมดในปีที่สอง และข้อมูลบางส่วนในปีที่สาม สุดท้ายนี้ เรายังมีตารางการวัดผล

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

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

หากเราดูข้อมูลของเรา คุณจะเห็นว่าเรามีคำสั่งซื้อที่แตกต่างกันในแต่ละวัน เป้าหมายคือนับทุกอย่างที่เริ่มตั้งแต่วันที่ 1 เมษายนเป็นวันทำการแรก วันที่ 2 เมษายนเป็นวันทำการที่สอง วันที่ 3 เมษายนเป็นวันทำการที่สาม และอื่นๆ เราจะนับจำนวนนี้ตั้งแต่ 1 ถึง N วันทำการในแต่ละเดือน โดยไม่รวมวันหยุดสุดสัปดาห์และวันหยุดนักขัตฤกษ์

การสร้างตารางซ้ำ

ในการเริ่มต้น เราจำเป็นต้องสร้างสำเนาของตารางนี้เพื่อให้เราสามารถรวมเป็นยอดขายรวมและนำเข้าสู่ระดับการสังเกตแต่ละรายการ เราจะเรียกตารางที่ซ้ำกันนี้ว่าSales Aggregated

คุณอาจสังเกตเห็นว่าเราจำลองตารางการขายแทนที่จะอ้างอิงถึงตารางนั้น ในกรณีนี้ เราจะรวมตารางนี้กับตารางการขายเดิม หากเราอ้างถึงมันจะทำให้เกิดปัญหาเมื่อเราเข้าร่วมทั้งสอง

สิ่งแรกที่ต้องทำคือใช้Group Byในคอลัมน์ OrderDate คลิกที่ปุ่มขั้นสูงเพราะจะทำให้เราเห็นภาพรวมของสิ่งที่เรากำลังทำอยู่ที่นี่ เราจะใส่Total Day Salesเป็นชื่อคอลัมน์ใหม่ของเรา และจะเป็นผลรวมของผลรวมของรายการทั้งหมดของเรา

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

การรวมตารางวันที่

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

เราจะรวมตารางDatesและเชื่อมโยงOrderDateกับDate เราสามารถเห็นที่ส่วนล่างว่าการเลือกตรงกับทุกแถวของตารางตามที่ควร

สาขาที่เราสนใจเป็นหลักคือIsBusinessDay

นี่คือฟิลด์หลักที่เราจะจัดกลุ่ม จากนั้นใช้ฟิลด์เดือน & ปีและเดือนปี

เรายังจำเป็นต้องตรวจสอบDayofWeekNameเพื่อให้แน่ใจว่าเรากำลังกรองวันที่เหมาะสมออกไป

เมื่อเราคลิกที่ปุ่ม ตกลงมันจะให้สิ่งที่เราต้องการ

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

จากนั้นเรากลับไปสร้างGroup By ที่ สอง

ในครั้งนี้ เราจะคลิกที่ปุ่มขั้นสูงจัดกลุ่มเป็นเดือนและปีจากนั้นจึงเพิ่มกลุ่มที่สอง

การจัดกลุ่มที่สองมีไว้สำหรับIsBusinessDayดังนั้นเราจึงสามารถกำหนดจำนวนวันที่มีวันทำการได้

จากนั้นเราจะสร้างฟังก์ชัน AllRowsเพื่อไม่ให้เกิดการรวม และเราจะสามารถกลับไปที่ระดับความละเอียดเดิมได้

นี่ควรให้ตารางที่ซ้อนกันซึ่งเราสามารถเห็นตารางสำหรับจริงและตารางสำหรับเท็จสำหรับแต่ละเดือน

การสร้างคอลัมน์แบบกำหนดเอง

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

เราจะใช้ฟังก์ชันที่เรียกว่าTable.AddIndexColumn ซึ่งจะทำงานในตาราง AllRowsและเราจะเรียกว่าDay Index เราต้องการให้ดัชนีเริ่มต้นด้วย 1 และเพิ่มขึ้นทีละ 1 ในทุกวันทำการใหม่

จากนั้น เราสามารถลบสามคอลัมน์แรกออก และขยายคอลัมน์ที่สี่ที่กำหนดเอง

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

กลับไปที่ตารางการขายของเรา เราจะรวมตารางรวมที่เราเพิ่งสร้างขึ้นกับตารางการขายของเรา

เราจะรวมเข้าด้วยกันในวันที่ OrderDate คุณสามารถดูได้ที่ด้านล่างว่าตรงกับแถวทั้งหมดจากตารางแรก

เมื่อเราขยายคอลัมน์ Sales Agg ให้เลือกฟิลด์ใหม่ที่เราเพิ่งเพิ่ม: คอลัมน์Month & Year , DayOfWeekName , MonthnYear , IsBusinessDayและDayIndex

มาล้างข้อมูลของเราก่อนที่เราจะคลิกปิด& นำไปใช้ มาย้ายตาราง Sales Aggในส่วนการเตรียมข้อมูล ของเรา จากนั้นยกเลิกการโหลดตารางนั้น เนื่องจากเราไม่จำเป็นต้องใช้ภายในโมเดลข้อมูล จากนั้นคลิกที่ปิด & นำไปใช้

ขณะนี้งานเตรียม Power Query ของเราเสร็จสิ้นแล้ว

การสร้างพารามิเตอร์แบบ What-If

โปรดจำไว้ว่าในงานเดิมเราต้องการเปลี่ยนจำนวนวันที่เราดูจาก 5 เป็น 10 และ 15 เป็น 20 ฉันคิดว่าวิธีที่ดีที่สุดในการทำเช่นนี้คือการใช้พารามิเตอร์ what- if ใหม่

เราจะเรียกพารามิเตอร์ what-if First N Business Daysและประเภทข้อมูลจะเป็นจำนวนเต็ม ขั้นต่ำถูกตั้งค่าเพิ่มขึ้นทีละ 1 และสูงสุด 20 จากนั้นเราจะตั้งค่าเริ่มต้นเป็น 5 ซึ่งเป็นค่าแรกที่ผู้ใช้ต้องการ

ขณะนี้เรามีตัวแบ่งส่วนข้อมูลที่สร้างการวัดการเก็บเกี่ยวโดยอัตโนมัติเพื่อบันทึกค่าตัวแบ่งส่วนข้อมูล

มาสร้างตารางพร้อมผลลัพธ์ของเรากัน ใส่ มิติ เดือน & ปีของ เรา ในแคนวาสแล้วจัดเรียงตามเดือน & ปี

เมื่อเราจัดเรียงฟิลด์นี้อย่างถูกต้องแล้ว เราก็สามารถทิ้ง การวัด ยอดขายรวมและขยายออกได้

การสร้างหน่วยวัดสำหรับจำนวนวันทำการ

เราจำเป็นต้องสร้างการวัดอีกหนึ่งรายการที่จะพิจารณาค่าของพารามิเตอร์แถบเลื่อนและคำนวณเฉพาะจำนวนวันทำการเท่านั้น

เราจะเรียกมาตรการใหม่นี้ว่าTotal Sales N Bus Days เราจะเริ่มต้นด้วย ฟังก์ชัน เนื่องจากเรากำลังเปลี่ยนแปลงบริบทอย่างแน่นอน และด้วยการวัดยอดขายรวมของเรา เราจะกรองสิ่งนี้ด้วยของตารางการขายของเรา

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

ตัวอย่างเช่น หากตัวเลื่อนอยู่ที่ 5 เราต้องการให้วันทั้งหมดของตารางการขายมีดัชนีวันน้อยกว่าหรือเท่ากับ 5

จากนั้นเราต้องการนำวันที่ไม่ใช่วันทำการออกไป เราจะอ้างอิงตารางวันที่และใช้ฟิลด์ IsBusinessDay เราจะดูเฉพาะผลลัพธ์ที่เป็น TRUE เท่านั้น

สุดท้าย นำบริบทกลับมาสำหรับเดือน & ปีและปิดการวัด เราน่าจะไปได้ดีในจุดนี้

มาวางมาตรการใหม่ลงในตารางของเรา

เราสามารถเลื่อนแถบเลื่อนไปที่เครื่องหมาย 10 วัน และตารางจะคำนวณแบบไดนามิกที่เครื่องหมาย 10 วัน

บทสรุป

ในเคล็ดลับ LuckyTemplates นี้ เราได้พูดคุยเกี่ยวกับวิธีการเปรียบเทียบแบบเหมือนแทนกันแบบไดนามิกโดยใช้โซลูชัน Power Query เราสามารถใส่ลงในแผนภูมิแท่งหรือแผนภูมิเส้น

ในบทช่วยสอนที่กำลังจะมีขึ้นบางส่วน เราจะดูที่โซลูชัน DAX และนำการแสดงภาพของ KPI scroller ไปใช้

หากคุณชอบเนื้อหาที่ครอบคลุมในบทช่วยสอนนี้ โปรดอย่าลืมสมัครรับข้อมูลจากช่องLuckyTemplates TV