12 เดือน เฉลี่ยเคลื่อนที่ สูตร

HOw ในการคำนวณ 12 เดือน rolling average คุณอาจต้องใช้ขั้นตอนสองขั้นตอนเพื่อทำสิ่งนี้ ขั้นตอนที่ 1: นับจำนวนวันสำหรับแต่ละเดือนนับ (วันที่) ForAll (วันที่) ForEach (เดือน) ขั้นตอนที่ 2: คำนวณมูลค่าทดสอบรวมสำหรับแต่ละเดือนผลรวม (ค่าทดสอบ) ForAll (วันที่) ForEach (เดือน) ขั้นตอนที่ 3: คำนวณ (นับจากวันที่ 1 ม. ค. 2 ก. พ. เป็นต้นไป) RunningCount (วันที่) ForAll (วันที่) ForEach (เดือน) ขั้นตอนที่ 4: คำนวณจำนวนวันทั้งหมดของ 12 เดือนที่ผ่านมา Count Count (วันที่) (วันที่) ForAll (วันที่) ForEach (เดือน) gt (สูงสุด (วันที่เริ่มทำงาน) ForAll (วันที่) ForEach (Month)) ใน Block) -12) ขั้นตอนที่ 5: คำนวณมูลค่าทดสอบทั้งหมดในช่วง 12 เดือนที่ผ่านมา (วันที่) ForAll (วันที่) ForEach (เดือน)) ใน Block -1)) ขั้นตอนที่ 6: คำนวณค่าเฉลี่ยถ่วงหมายเหตุ: คุณ อาจสร้างตัวแปรใหม่สำหรับแต่ละขั้นตอนข้างต้น แต่อย่าใช้ตัวแปรใหม่เหล่านี้ในการคำนวณตั้งแต่ขั้นตอนที่ 1 ถึง 5 สูตรทั้งหมดข้างต้นต้องอยู่ในรูปแบบที่แน่นอน มิฉะนั้นบริบทการคำนวณใน webi จะไม่สามารถสร้างผลลัพธ์ที่คาดหวังได้ ฉันหวังว่าวิธีนี้จะช่วยให้เฉลี่ย 12 เดือนเฉลี่ยใน DAX การคำนวณค่าเฉลี่ย 12 เดือนโดยเฉลี่ยใน DAX ดูเหมือนจะเป็นงานง่ายๆ แต่จะซ่อนความซับซ้อนบางอย่าง บทความนี้อธิบายถึงวิธีการเขียนสูตรที่ดีที่สุดเพื่อหลีกเลี่ยงข้อผิดพลาดทั่วไปที่ใช้ฟังก์ชันอัจฉริยะเวลา เราเริ่มต้นด้วยแบบจำลองข้อมูล AdventureWorks ปกติโดยมีตารางผลิตภัณฑ์การขายและปฏิทิน ปฏิทินมีการทำเครื่องหมายเป็นตารางปฏิทิน (จำเป็นต้องทำงานกับฟังก์ชันข่าวกรองในเวลาใด ๆ ) และเราสร้างลำดับชั้นที่เรียบง่ายขึ้นเมื่อเดือนปีเดือน ด้วยการตั้งค่านี้เป็นเรื่องง่ายมากที่จะสร้าง PivotTable แรกที่แสดงยอดขายเมื่อเวลาผ่านไป: เมื่อทำการวิเคราะห์แนวโน้มถ้ายอดขายขึ้นอยู่กับฤดูกาลหรือโดยทั่วไปถ้าคุณต้องการลบผลกระทบของยอดขายและการลดลงของยอดขาย เทคนิคทั่วไปคือการคำนวณค่าในช่วงเวลาที่กำหนดโดยปกติแล้วจะเป็น 12 เดือนและโดยเฉลี่ย ค่าเฉลี่ยของการกลิ้งมากกว่า 12 เดือนแสดงให้เห็นถึงแนวโน้มที่ดีและมีประโยชน์มากในแผนภูมิ เมื่อได้รับวันที่เราสามารถคำนวณค่าเฉลี่ยกลิ้ง 12 เดือนโดยใช้สูตรนี้ซึ่งยังคงมีปัญหาบางอย่างที่เราจะแก้ในภายหลัง: ลักษณะการทำงานของสูตรง่าย: คำนวณมูลค่าการขายหลังจากสร้างตัวกรองในปฏิทินที่ แสดงข้อมูลครบหนึ่งปี หลักของสูตรคือ DATESBETWEEN ซึ่งจะส่งคืนชุดวันที่ระหว่างขอบเขตทั้งสอง ด้านล่างคือ: การอ่านจากด้านในสุด: ถ้าเราแสดงข้อมูลเป็นเดือนหนึ่งพูดว่ากรกฎาคม 2007 เราจะใช้วันที่มองเห็นล่าสุดโดยใช้ LASTDATE ซึ่งจะส่งกลับในวันสุดท้ายในเดือนกรกฎาคม 2550 จากนั้นเราจะใช้ NEXTDAY เพื่อเข้าร่วมการแข่งขันครั้งที่ 1 ของเดือนสิงหาคม 2007 และในที่สุดเราก็ใช้ SAMEPERIODLASTYEAR เพื่อเปลี่ยนกลับมาเป็นเวลาหนึ่งปีโดยให้ผลผลิตตั้งแต่วันที่ 1 สิงหาคม 2549 ขอบเขตด้านบนเป็นเพียงแค่ LASTDATE คือปลายเดือนกรกฎาคม 2007 หากเราใช้สูตรนี้ในตารางสาระสำคัญผลลัพธ์ก็ดูดี แต่เรา มีปัญหาสำหรับวันที่ล่าสุด: ในความเป็นจริงตามที่เห็นในรูปภาพค่าจะถูกคำนวณอย่างถูกต้องจนถึงปี 2008 จากนั้นไม่มีค่าในปี 2009 (ซึ่งเป็นราคาที่ถูกต้องเราไม่มียอดขายในปี 2009) แต่มี มูลค่าที่น่าแปลกใจในเดือนธันวาคม 2010 ซึ่งสูตรของเราแสดงยอดรวมทั้งหมดแทนที่จะเป็นมูลค่าที่ว่างเปล่าตามที่เราคาดไว้ ในความเป็นจริงเมื่อวันที่ธันวาคม LASTDATE จะส่งคืนในวันสุดท้ายของปีและ NEXTDAY จะกลับวันที่ 1 มกราคม 2011 แต่ NEXTDAY เป็นฟังก์ชันข้อมูลอัจฉริยะเวลาและคาดว่าจะส่งคืนชุดวันที่ที่มีอยู่ ความจริงข้อนี้ไม่ค่อยชัดเจนเท่าไรนัก ฟังก์ชันอัจฉริยะเวลาไม่ได้ทำคณิตศาสตร์ในวันที่ ถ้าคุณต้องการใช้วันหลังจากวันที่ที่ระบุคุณสามารถเพิ่ม 1 ลงในคอลัมน์วันใดก็ได้และผลลัพธ์จะเป็นวันถัดไป แต่ฟังก์ชันการทำงานของข้อมูลอัจฉริยะเวลาจะเปลี่ยนชุดวันที่ไปมาตลอดเวลา ดังนั้น NEXTDAY ใช้ข้อมูลของตน (ในกรณีของเราคือตารางแถวเดียวที่มีวันที่ 31 ธันวาคม 2010) และจะเลื่อนออกไปหนึ่งวันในภายหลัง ปัญหาคือผลลัพธ์ควรเป็น 1 มกราคม 2011 แต่เนื่องจากตารางปฏิทินไม่ประกอบด้วยวันที่ดังกล่าวผลลัพธ์คือ BLANK ดังนั้นนิพจน์ของเราคำนวณยอดขายที่มีขอบเขตต่ำกว่าที่ว่างเปล่านั่นหมายถึงจุดเริ่มต้นของเวลาทำให้ผลผลิตรวมเป็นยอดขาย หากต้องการแก้ไขสูตรก็เพียงพอที่จะเปลี่ยนลำดับการประเมินผลของขอบเขตล่าง: ตามที่คุณเห็นตอนนี้เรียกว่า NEXTDAY หลังการเปลี่ยนแปลงหนึ่งปีย้อนหลัง ด้วยวิธีนี้เราใช้เวลา 31 ธันวาคม 2010 ย้ายไปที่ 31 ธันวาคม 2009 และใช้วันถัดไปซึ่งเป็นวันที่ 1 มกราคม 2010: วันที่มีอยู่ในตารางปฏิทิน ผลที่ได้คือตอนนี้เป็นที่คาดหวัง: ในตอนนี้เราต้องแบ่งเฉพาะตัวเลขดังกล่าวเป็น 12 เพื่อให้ได้ค่าเฉลี่ยของการกลิ้ง แต่เท่าที่คุณสามารถนึกภาพได้ง่ายเราไม่สามารถหารด้วย 12 ได้อย่างแท้จริงในตอนต้นของช่วงเวลาไม่มี 12 เดือนนับรวม แต่เป็นตัวเลขที่ต่ำกว่า เราจำเป็นต้องคำนวณจำนวนเดือนที่มียอดขาย ซึ่งสามารถทำได้โดยใช้การกรองข้ามของตารางปฏิทินด้วยตารางการขายหลังจากที่เราใช้บริบทใหม่ 12 เดือน เรากำหนดมาตรการใหม่ที่คำนวณจำนวนเดือนที่มีอยู่ในช่วง 12 เดือน: คุณสามารถดูในรูปถัดไปว่าการวัด Month12M คำนวณค่าที่ถูกต้อง: เป็นมูลค่าที่ควรทราบว่าสูตรนี้ไม่ได้ผลถ้าคุณเลือกช่วงเวลา นานกว่า 12 เดือนเนื่องจาก CalendarMonthName มีเพียง 12 ค่าเท่านั้น หากต้องการระยะเวลานานขึ้นคุณจะต้องใช้คอลัมน์ YYYYMM เพื่อให้สามารถนับได้มากกว่า 12 ส่วนที่น่าสนใจของสูตรนี้ที่ใช้การกรองข้ามคือการคำนวณจำนวนเดือนที่สามารถใช้งานได้แม้ว่าคุณจะใช้ตัวกรองอื่น แอตทริบิวต์ ตัวอย่างเช่นหากคุณเลือกสีฟ้าโดยใช้เครื่องตัดแล้วการขายจะเริ่มขึ้นในเดือนกรกฎาคม 2007 (ไม่ใช่ในปี 2005 เนื่องจากเกิดขึ้นกับสีอื่น ๆ ) การใช้ตัวกรองไขว้ในการขายสูตรนี้คำนวณได้อย่างถูกต้องว่าในเดือนกรกฎาคม 2007 มียอดขายใน Blue เพียงเดือนเดียว: ณ ขณะนี้ค่าเฉลี่ยของการกลิ้งคือ DIVIDE: เมื่อเราใช้ในตาราง Pivot เรายังคงใช้ มีปัญหาเล็กน้อย: ในความเป็นจริงค่าจะคำนวณเป็นเดือนที่ไม่มีการขาย (เช่นเดือนในอนาคต): สามารถแก้ไขได้โดยใช้คำสั่ง IF เพื่อป้องกันไม่ให้สูตรแสดงค่าเมื่อไม่มียอดขาย ฉันไม่มีอะไรต่อต้าน IF แต่สำหรับการติดยาเสพติดในหมู่คุณคุณควรจดจำว่า IF อาจเป็นฆาตกรที่มีประสิทธิภาพเนื่องจากอาจบังคับให้เครื่องยนต์สูตร DAX เตะขึ้นมาในกรณีเฉพาะนี้ความแตกต่างเล็กน้อย แต่ ตามกฎทั่วไปวิธีที่ดีที่สุดในการลบคุณค่าเมื่อไม่มีการขายคือการพึ่งพาสูตรเครื่องมือเก็บข้อมูลแบบสแตนด์อโลนเช่นนี้เปรียบเทียบแผนภูมิโดยใช้ Avg12M กับอีกรุ่นหนึ่งที่แสดงยอดขาย แสดงแนวโน้มในแบบที่สะอาดมาก: แจ้งให้ฉันทราบเกี่ยวกับบทความที่กำลังจะมาถึง (จดหมายข่าว) ยกเลิกการเลือกดาวน์โหลดไฟล์อย่างอิสระค่าเฉลี่ยตัวอย่างนี้สอนวิธีคำนวณค่าเฉลี่ยเคลื่อนที่ของชุดเวลาใน Excel ค่าเฉลี่ยเคลื่อนที่จะใช้เพื่อทำให้เกิดความผิดปกติ (ยอดเขาและหุบเขา) เพื่อรับรู้แนวโน้มได้ง่ายขึ้น 1. ขั้นแรกให้ดูที่ซีรี่ส์เวลาของเรา 2. ในแท็บข้อมูลคลิกการวิเคราะห์ข้อมูล หมายเหตุ: ไม่สามารถหาปุ่ม Data Analysis คลิกที่นี่เพื่อโหลด Add-in Analysis ToolPak 3. เลือก Moving Average และคลิก OK 4. คลิกที่กล่อง Input Range และเลือกช่วง B2: M2 5. คลิกที่ช่อง Interval และพิมพ์ 6. 6. คลิกที่ Output Range box และเลือก cell B3 8. วาดกราฟของค่าเหล่านี้ คำอธิบาย: เนื่องจากเราตั้งค่าช่วงเป็น 6 ค่าเฉลี่ยเคลื่อนที่คือค่าเฉลี่ยของ 5 จุดข้อมูลก่อนหน้าและจุดข้อมูลปัจจุบัน เป็นผลให้ยอดเขาและหุบเขาจะเรียบออก กราฟแสดงแนวโน้มที่เพิ่มขึ้น Excel ไม่สามารถคำนวณค่าเฉลี่ยเคลื่อนที่สำหรับจุดข้อมูล 5 จุดแรกได้เนื่องจากไม่มีจุดข้อมูลก่อนหน้านี้เพียงพอ 9. ทำซ้ำขั้นตอนที่ 2 ถึง 8 สำหรับช่วงที่ 2 และช่วงที่ 4 ข้อสรุป: ช่วงที่ใหญ่กว่ายอดเนินและหุบเขาจะยิ่งเรียบขึ้น ระยะห่างที่เล็กลงค่าเฉลี่ยของค่าเฉลี่ยเคลื่อนที่ที่ใกล้เคียงกับค่าข้อมูลจริงมากขึ้น