รับค่า LuckyTemplates สัปดาห์ก่อนหน้าโดยใช้ DAX และ Power Query

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

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

สารบัญ

• ค่าสัปดาห์ก่อนหน้าใน LuckyTemplates: ปัญหา
• รับ LuckyTemplates เมื่อสัปดาห์ก่อนโดยใช้ DAX
• บทสรุป

ค่าสัปดาห์ก่อนหน้าใน LuckyTemplates: ปัญหา

ถ้าเราดูที่สัปดาห์ เรามี 52 สัปดาห์; คูณด้วยเจ็ดวัน (หนึ่งสัปดาห์) และเท่ากับ 364 ดังนั้น ในแต่ละปี จะมีวันพิเศษเพิ่มขึ้น 1 วัน และสำหรับปีอธิกสุรทิน จะมีวันพิเศษอีก 2 วัน หนึ่งหรือสองวันพิเศษเหล่านี้จบลงด้วยการสร้างปัญหามากมาย

มาสร้างหน่วยวัดจากตาราง Dates กันเถอะ เราจะเรียกมันว่าMAX สัปดาห์ # (หมายเลข) ในการวิเคราะห์นี้ เราใช้ตารางวันที่ขยายของ LuckyTemplates สิ่งนี้มีISO Weeknumberซึ่งมีประโยชน์มาก ทีนี้ มาดู MAX ของ ISO Weeknumber กัน

ตอนนี้ มาดูตารางและวางปีและMAX สัปดาห์ # ของ เรา ชุดข้อมูลนี้มีข้อมูล 10 ปี และเราเห็นว่า 2 ปีแรกมี 52 สัปดาห์ จากนั้น เรามีเวลาสองปีกับ 53 สัปดาห์ สามปีกับ 52 สัปดาห์ และอีกสองปีกับ 53 และอีกหนึ่งปีกับ 52

ไม่มีรูปแบบที่ชัดเจนที่นี่ มันไม่ง่ายเลยที่จะย้อนกลับไปทำนายว่าปีหนึ่งคุณจะมี 52 สัปดาห์ และอีกปีหนึ่งคุณจะมี 53 สัปดาห์

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

ในตารางวันที่ขยายของ LuckyTemplates ที่เรา กำลังใช้อยู่ Melissa (หนึ่งในผู้เชี่ยวชาญของ LuckyTemplates) ได้รวมฟังก์ชันที่เรียกว่าOFFSETS มาดูฟังก์ชันนี้กันเพราะมันเป็นกุญแจสำคัญในการแก้ปัญหานี้จากมุมมองของ DAX

เมื่อพิจารณาจากช่วงเวลาปัจจุบัน (เช่น วันที่ 20 มกราคมซึ่งเป็นช่วงเวลาของการสร้างบทช่วยสอนนี้) สิ่งที่เราเห็นคือค่าชดเชยเท่ากับศูนย์ วิธีการทำงานของออฟเซ็ตคือสำหรับงวดปัจจุบัน ค่าออฟเซ็ตจะเป็นศูนย์

สัปดาห์ก่อนสัปดาห์ปัจจุบันเป็นลบ สองสัปดาห์ก่อนสัปดาห์ปัจจุบันเป็นลบ 2 เป็นต้น จากนั้นมันจะไปข้างหน้าจากจำนวนบวก อาทิตย์หน้าได้หนึ่ง สองสัปดาห์ได้สอง ฯลฯ

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

รับ LuckyTemplates เมื่อสัปดาห์ก่อนโดยใช้ DAX

มาสร้างหน่วยวัดใหม่และเรียกมันว่าPrevious Wk # เราจะเริ่มต้นด้วยตัวแปร ( SelWkOffset )ซึ่งเราจะทำSELECTEDVALUEของWeekOffset ในแถวปัจจุบัน มันจะวาด WeekOffset นั้นและใส่ในตัวแปรของเรา

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

จากนั้น เราต้องการMAX ISO Weeknumberซึ่งจะขึ้นอยู่กับเงื่อนไขการกรองเหล่านี้ ดังนั้น เราจะลบตัวกรองทั้งหมดในตารางวันที่ ดังนั้นเราจะใช้ทั้งหมดที่นี่ หากเรามีตัวแบ่งส่วนข้อมูลวันที่ เราอาจต้องการใช้ALLSELECTEDแต่ตอนนี้ยังไม่มี ดังนั้นเราจะใช้ ALL เพื่อลบตัวกรองออกจากตาราง Dates นั้น

จากนั้น เราจะบอกว่าWeekOffset จะเท่ากับ SelWkOffset ลบ 1 นี่คือจุดที่ OFFSETS มีประสิทธิภาพมาก คุณไม่สามารถใช้ Weeknumber ลบหนึ่งได้ เนื่องจากจะรีเซ็ตทุกปี แต่เนื่องจากออฟเซ็ตทั้งหมดเป็นแบบลำดับ คุณจึงสามารถดำเนินการได้เหมือนกับที่คุณปฏิบัติกับการค้นหาปีที่แล้ว และเพียงแค่ลบออกหนึ่งรายการสำหรับแต่ละปีก่อนหน้า และนี่ ออฟเซ็ตนี้ทำงานในลักษณะเดียวกับจำนวนที่ต่อเนื่องกัน

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

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

ทีนี้ คุณจะทำอย่างไรในกรณีที่คุณไม่มีออฟเซ็ต?

สมมติว่าคุณกำลังทำงานกับตารางวันที่ของบริษัทที่มาจากคลังข้อมูลของคุณและตารางนั้นไม่ได้รวมออฟเซ็ตเหล่านี้ มีเทคนิคหนึ่งที่ฉันอยากจะแสดงให้คุณเห็นใน Power Query ครั้งแรกที่ฉันเห็นมันในรายการบล็อกโดย Imke Feldmann

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

ลองอ้างอิงตาราง Dates Raw นี้แล้วเราจะเรียกสิ่งนี้ว่าDates จากนั้น เราตรวจสอบให้แน่ใจว่าได้จัดเรียงสิ่งนี้ตามลำดับจากน้อยไปมาก

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

ต่อไปเราต้องการเพิ่มคอลัมน์ดัชนีเหล่านั้น สิ่งสำคัญในที่นี้คือดัชนีแรกที่เราจะเพิ่มจะต้องเป็นดัชนีที่มีฐานเป็น 0 จาก���ั้น เราจะเพิ่มดัชนีตัวที่สองที่เป็นดัชนีอิง 1 ตัว

จากนั้น เราจะนำคอลัมน์นี้มารวมตารางเข้าด้วยกัน เราจะรวมเข้าด้วยกันโดยอิงจากสองคอลัมน์ดัชนีที่แตกต่างกัน นั่นจะทำให้เราเปลี่ยนแถว

ดังนั้น หากเราใช้สิ่งนี้และรวมดัชนีตัวแรกเข้ากับดัชนีอิง 0 กับดัชนีอิง 1 ก็จะจับคู่ 521 ที่ 522 แถว นั่นคือสิ่งที่เราคาดหวังเพราะตารางที่สองจะไม่รับดัชนีศูนย์เนื่องจากเริ่มต้นที่หนึ่ง

หลังจากนั้น เราจะขยายมันออกไปอย่างถูกต้อง เพียงแค่ใช้ ISO Weeknumber

นั่นทำให้เรามีจำนวนสัปดาห์ก่อนหน้า และตอนนี้ เราจะขยายการจัดกลุ่มเดิมของเราและนำฟิลด์ WeekEnding และหมายเลขสัปดาห์ ISO เดิมออก เพื่อไม่ให้เราซ้ำกัน

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

บทสรุป

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

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

ไชโย!