ตารางวันที่ใน Power Pivot นั้นมีความเป็นสิ่งพื้นฐานในการเรียกดูและการคํานวณข้อมูลเมื่อเวลาผ่านไป บทความนี้จะให้ความเข้าใจโดยละเอียดเกี่ยวกับตารางวันที่และวิธีที่คุณสามารถสร้างตารางใน Power Pivot โดยเฉพาะอย่างยิ่ง บทความนี้อธิบาย:
-
เหตุใดตารางวันที่จึงสําคัญในการเรียกดูและการคํานวณข้อมูลตามวันที่และเวลา
-
วิธีใช้ Power Pivot เพื่อเพิ่มตารางวันที่ลงในตัวแบบข้อมูล
-
วิธีสร้างคอลัมน์วันที่ใหม่ เช่น ปี เดือน และระยะเวลาในตารางวันที่
-
วิธีสร้างความสัมพันธ์ระหว่างตารางวันที่และตารางข้อเท็จจริง
-
วิธีการร่วมงานกับเวลา
บทความนี้มีไว้เพื่อให้ผู้ใช้ใหม่เกี่ยวกับ Power Pivot อย่างไรก็ตาม การนําเข้าข้อมูล การสร้างความสัมพันธ์ และการสร้างคอลัมน์และการวัดที่ได้คํานวณนั้นเป็นเรื่องสําคัญ
บทความนี้ ไม่ได้อธิบายถึง วิธีการใช้ฟังก์ชัน DAX Time-Intelligenceในการวัดสูตร For more information about how to create measures with DAX Time Intelligence functions, see Time Intelligence in Power Pivot in Excel.
หมายเหตุ: ใน Power Pivot ชื่อ "measure" และ "เขตข้อมูลที่คํานวณ" คือแบบเดียวกัน เราใช้การวัดชื่อตลอดบทความนี้ ดูข้อมูลเพิ่มเติมที่การวัดใน Power Pivot
เนื้อหา
การเข้าใจตารางวันที่
การวิเคราะห์ข้อมูลเกือบทั้งหมดเกี่ยวข้องกับการเรียกดูและการเปรียบเทียบข้อมูลตามวันที่และเวลา ตัวอย่างเช่น คุณอาจต้องการรวมยอดขายของไตรมาสงบประมาณในอดีต แล้วเปรียบเทียบผลรวมเหล่านั้นกับไตรมาสอื่น หรือคุณอาจต้องการคํานวณยอดดุลปิดท้ายเดือนของบัญชี ในแต่ละกรณี คุณจะใช้วันที่เป็นวิธีการจัดกลุ่มและรวมธุรกรรมการขายหรือยอดดุลของช่วงเวลาใดช่วงเวลาหนึ่ง
Power Viewรายงาน
ตารางวันที่สามารถมีการแสดงวันที่และเวลาที่แตกต่างกันมากมาย ตัวอย่างเช่น ตารางวันที่มักจะมีคอลัมน์ เช่น ปีงบประมาณ, เดือน, ไตรมาส หรือช่วงเวลา ซึ่งคุณสามารถเลือกเป็นเขตข้อมูลจากรายการเขตข้อมูลเมื่อกดและกรองข้อมูลของคุณในรายงาน PivotTable หรือ Power View ได้
Power Viewรายการเขตข้อมูล
สําหรับคอลัมน์วันที่ เช่น ปี เดือน และไตรมาส เพื่อรวมวันที่ทั้งหมดภายในช่วงที่ตามลําดับตารางวันที่ต้องมีอย่างน้อยหนึ่งคอลัมน์ที่มีชุดของวันที่ที่อยู่ติดกัน นั่นคือ คอลัมน์นั้นต้องมีหนึ่งแถวทุกวันของแต่ละปีซึ่งรวมอยู่ในตารางวันที่
ตัวอย่างเช่น ถ้าข้อมูลที่คุณต้องการเรียกดูมีวันที่ตั้งแต่ 1 กุมภาพันธ์ 2553 ถึงวันที่ 30 พฤศจิกายน 2555 และรายงานคุณบนปีปฏิทิน คุณจะต้องใช้ตารางวันที่ที่มีวันที่อย่างน้อยหนึ่งช่วงตั้งแต่วันที่ 1 มกราคม 2553 ถึง 31 ธันวาคม 2555 ทุกปีในตารางวันที่ของคุณต้องมีวันทั้งหมดของแต่ละปี ถ้าคุณจะรีเฟรชข้อมูลของคุณด้วยข้อมูลที่ใหม่กว่าเป็นปกติ คุณอาจต้องการเรียกใช้วันที่สิ้นสุดเป็นปีหรือสองปี ดังนั้น คุณจึงไม่ต้องอัปเดตตารางวันที่ของคุณตามเวลา
ตารางวันที่ที่มีชุดของวันที่ที่อยู่ติดกัน
ถ้าคุณรายงานเกี่ยวกับปีงบประมาณ คุณสามารถสร้างตารางวันที่ที่มีชุดของวันที่ต่อเนื่องกันของปีงบประมาณแต่ละปีได้ ตัวอย่างเช่น ถ้าปีงบประมาณของคุณเริ่มต้นในวันที่ 1 มีนาคม และคุณมีข้อมูลปีงบประมาณ 2010 จนถึงวันที่ปัจจุบัน (ตัวอย่างเช่น ใน FY 2013) คุณสามารถสร้างตารางวันที่ที่เริ่มต้นในวันที่ 1/3/2552 และรวมอย่างน้อยทุกวันในปีงบประมาณแต่ละปีจนถึงวันสุดท้ายในปีงบประมาณ 2556
ถ้าคุณจะรายงานทั้งปีปฏิทินและปีงบประมาณ คุณไม่าเป็นต้องสร้างตารางวันที่แยกต่างหาก ตารางวันที่เดียวสามารถรวมคอลัมน์ต่างๆ ของปีปฏิทิน ปีงบประมาณ และแม้แต่ปฏิทินช่วงเวลาสี่สัปดาห์ที่สิบสาม สิ่งสําคัญคือตารางวันที่ของคุณประกอบด้วยชุดของวันที่ที่ต่อเนื่องกันของทั้งปี
การเพิ่มตารางวันที่ลงในตัวแบบข้อมูล
คุณสามารถเพิ่มตารางวันที่ลงในตัวแบบข้อมูลได้หลายวิธีดังนี้
-
นําเข้าจากฐานข้อมูลเชิงสัมพันธ์ หรือแหล่งข้อมูลอื่น
-
สร้างตารางวันที่Excel จากนั้นคัดลอกหรือลิงก์ไปยังตารางใหม่ใน Power Pivot
-
นําเข้า Microsoft Azure Marketplace
มาดูกันว่าสิ่งเหล่านี้ใกล้เคียงกับกันมาก
นําเข้าจากฐานข้อมูลเชิงสัมพันธ์
ถ้าคุณนําเข้าข้อมูลบางส่วนหรือทั้งหมดของคุณจากคลังสินค้าข้อมูลหรือฐานข้อมูลเชิงสัมพันธ์ชนิดอื่นๆ มีโอกาสมีตารางวันที่และความสัมพันธ์ระหว่างตารางกับข้อมูลที่เหลือที่คุณนําเข้าอยู่แล้ว วันที่และรูปแบบน่าจะตรงกับวันที่ในข้อมูลข้อเท็จจริงของคุณ และวันที่อาจเริ่มได้ดีในอดีตและไปไกลในอนาคต ตารางวันที่ที่คุณต้องการนําเข้าอาจมีขนาดใหญ่และมีช่วงวันที่เกินกว่าที่คุณจะต้องรวมไว้ในตัวแบบข้อมูลของคุณ คุณสามารถใช้ฟีเจอร์ตัวกรองขั้นสูงของตัวช่วยสร้างการนําเข้าตารางของ Power Pivot เพื่อเลือกเฉพาะวันที่และคอลัมน์ที่คุณต้องการจริงๆ ซึ่งสามารถลดขนาดและประสิทธิภาพของเวิร์กบุ๊กลงได้อย่างมาก
ตัวช่วยสร้างการนําเข้าตาราง
ในกรณีส่วนใหญ่ คุณจะไม่ต้องสร้างคอลัมน์เพิ่มเติม เช่น ปีงบประมาณสัปดาห์ชื่อเดือน เป็นต้น เนื่องจากคอลัมน์เหล่านั้นจะมีอยู่แล้วในตารางที่นําเข้า อย่างไรก็ตาม ในบางกรณี หลังจากที่คุณได้นําเข้าตารางวันที่ลงในตัวแบบข้อมูลของคุณแล้ว คุณอาจต้องสร้างคอลัมน์วันที่เพิ่มเติม โดยขึ้นอยู่กับความต้องการด้านการรายงานที่เฉพาะเจาะจง แต่โชคดีที่การใช้ DAX นั้นง่าย คุณจะเรียนรู้เพิ่มเติมเกี่ยวกับการสร้างเขตข้อมูลตารางวันที่ในภายหลัง สภาพแวดล้อมทั้งหมดจะแตกต่างกัน ถ้าคุณไม่แน่ใจว่าแหล่งข้อมูลของคุณมีตารางวันที่หรือปฏิทินที่เกี่ยวข้องหรือไม่ ให้ติดต่อผู้ดูแลระบบฐานข้อมูลของคุณ
สร้างตารางวันที่ในExcel
คุณสามารถสร้างตารางวันที่ใน Excelจากนั้นคัดลอกลงในตารางใหม่ในตัวแบบข้อมูล ซึ่งค่อนข้างง่ายที่จะให้คุณมีความยืดหยุ่นมาก
เมื่อคุณสร้างตารางวันที่ใน Excel คุณจะเริ่มต้นด้วยคอลัมน์เดียวที่มีช่วงวันที่ต่อเนื่องกัน จากนั้น คุณสามารถสร้างคอลัมน์เพิ่มเติม เช่น ปี ไตรมาส เดือน ปีงบประมาณ ช่วงเวลา เป็นต้น ในเวิร์กชีต Excel โดยใช้สูตร Excel หรือหลังจากที่คุณคัดลอกตารางลงในตัวแบบข้อมูล คุณสามารถสร้างเป็นคอลัมน์ที่คํานวณได้ การสร้างคอลัมน์วันที่เพิ่มเติมใน Power Pivot ได้อธิบายไว้ในส่วน การเพิ่มคอลัมน์วันที่ใหม่ ลงในส่วน ตารางวันที่ ต่อไปในบทความนี้
วิธีการ: สร้างตารางวันที่ใน Excelและคัดลอกลงในตัวแบบข้อมูล
-
ใน Excel ในเวิร์กชีตเปล่า ในเซลล์A1ให้พิมพ์ชื่อส่วนหัวของคอลัมน์เพื่อระบุช่วงของวันที่ โดยทั่วไปแล้ว ค่านี้จะ เหมือนกับ Date, DateTime หรือ DateKey
-
ในเซลล์ A2ให้พิมพ์วันที่เริ่มต้น ตัวอย่างเช่น1/1/2553
-
คลิกจุดจับเติม แล้วลากลงไปยังหมายเลขแถวที่มีวันที่สิ้นสุด ตัวอย่างเช่น31/12/2016
-
เลือกแถวทั้งหมดใน คอลัมน์ วันที่ (รวมถึงชื่อส่วนหัวในเซลล์ A1)
-
ในกลุ่มสไตล์ ให้คลิกจัดรูปแบบเป็นตาราง แล้วเลือกสไตล์
-
ในกล่องโต้ตอบ จัดรูปแบบ เป็นตาราง ให้คลิกตกลง
-
คัดลอกแถวทั้งหมด รวมถึงส่วนหัว
-
ใน Power Pivot บนแท็บ หน้าแรก ให้คลิกวาง
-
ในการวาง>ชื่อตาราง ให้พิมพ์ชื่อเช่นวันที่ หรือปฏิทิน ปล่อยให้ใช้แถวแรกเป็น ส่วนหัวของคอลัมน์ ที่เลือกไว้ แล้วคลิกตกลง
ตารางวันที่ใหม่ (ชื่อ ปฏิทิน ในตัวอย่างนี้) ใน Power Pivot จะมีลักษณะดังนี้
หมายเหตุ: คุณยังสามารถสร้างตารางที่ลิงก์โดยใช้เพิ่มไปยังตัวแบบข้อมูล อย่างไรก็ตาม การสิ่งนี้จะทให้เวิร์กบุ๊กของคุณมีขนาดใหญ่โดยไม่จําเป็นเนื่องจากเวิร์กบุ๊กมีตารางวันที่สองเวอร์ชัน ใน Excel และใน Power Pivot
หมายเหตุ: วันที่ ของ ชื่อเป็นคีย์เวิร์ดใน Power Pivot ถ้าคุณตั้งชื่อตารางที่คุณสร้างใน Power Pivot Date คุณจะต้องใส่ชื่อตารางด้วยเครื่องหมายอัญประกาศเดี่ยวในสูตร DAX ใดๆ ที่อ้างอิงในอาร์กิวเมนต์ รูปภาพและสูตรตัวอย่างทั้งหมดในบทความนี้อ้างอิงถึงตารางวันที่ที่สร้างใน Power Pivot ที่ชื่อว่าปฏิทิน
ตอนนี้คุณมีตารางวันที่ในตัวแบบข้อมูลของคุณแล้ว คุณสามารถเพิ่มคอลัมน์วันที่ใหม่ เช่น ปี เดือน เป็นต้น โดยใช้ DAX
การเพิ่มคอลัมน์วันที่ใหม่ลงในตารางวันที่
ตารางวันที่ที่มีคอลัมน์วันที่เดียวที่มีหนึ่งแถวในแต่ละวันของแต่ละปีเป็นเรื่องสําคัญในการระบุวันที่ทั้งหมดในช่วงวันที่ นอกจากนี้ยังจําเป็นต้องสร้างความสัมพันธ์ระหว่างตารางข้อเท็จจริงและตารางวันที่ด้วย แต่คอลัมน์วันที่เดี่ยวที่มีหนึ่งแถวในแต่ละวันจะไม่มีประโยชน์เมื่อวิเคราะห์ตามวันที่ในรายงาน PivotTable Power Viewวันที่ คุณต้องการให้ตารางวันที่ของคุณรวมคอลัมน์ที่ช่วยให้คุณรวมข้อมูลของคุณในช่วงหรือกลุ่มวันที่ ตัวอย่างเช่น คุณอาจต้องการรวมยอดขายตามเดือนหรือไตรมาส หรือคุณอาจสร้างการวัดที่คํานวณการเติบโตในช่วงปีเดียวกัน ในแต่ละกรณี ตารางวันที่ของคุณต้องการคอลัมน์ปี เดือน หรือไตรมาส ที่ช่วยให้คุณรวมข้อมูลของคุณในช่วงเวลาดังกล่าวได้
ถ้าคุณนําเข้าตารางวันที่ของคุณจากแหล่งข้อมูลที่สัมพันธ์กัน ตารางอาจมีคอลัมน์วันที่ชนิดต่างๆ ที่คุณต้องการอยู่แล้ว ในบางกรณี คุณอาจต้องการปรับเปลี่ยนบางคอลัมน์หรือสร้างคอลัมน์วันที่เพิ่มเติม โดยเฉพาะอย่างยิ่งถ้าคุณสร้างตารางวันที่ของคุณเองใน Excelคัดลอกลงในตัวแบบข้อมูล โชคดีที่การสร้างคอลัมน์วันที่ใหม่ใน Power Pivot นั้นค่อนข้างง่ายด้วย ฟังก์ชันวันที่และเวลา ใน DAX
เคล็ดลับ: ถ้าคุณยังไม่ได้ใช้งานกับ DAX ซึ่งเป็นที่ที่ยอดเยี่ยมในการเริ่มการเรียนรู้ด้วยการเริ่มต้นใช้งานด่วน: เรียนรู้ข้อมูลพื้นฐานเกี่ยวกับ DAX ภายใน 30นาทีOffice.com
ฟังก์ชันวันที่และเวลาของ DAX
ถ้าคุณเคยใช้งานฟังก์ชันวันที่และเวลาในExcelสูตรต่างๆ คุณอาจจะคุ้นเคยกับ ฟังก์ชันวันที่และเวลา แม้ว่าฟังก์ชันเหล่านี้จะคล้ายคลึงกับคู่Excel แต่มีความแตกต่างที่สําคัญบางอย่าง:
-
ฟังก์ชันวันที่และเวลาของ DAX จะใช้ชนิดข้อมูลเวลาวันที่
-
ซึ่งอาจใช้เวลาค่าจากคอลัมน์เป็นอาร์กิวเมนต์
-
ซึ่งสามารถใช้เพื่อส่งกลับและ/หรือจัดการค่าวันที่ได้
ฟังก์ชันเหล่านี้มักจะถูกใช้เมื่อสร้างคอลัมน์วันที่แบบปรับแต่งเองในตารางวันที่ ดังนั้นจึงเป็นเรื่องสําคัญที่ควรเข้าใจ เราจะใช้ฟังก์ชันเหล่านี้จํานวนหนึ่งเพื่อสร้างคอลัมน์ปี ไตรมาส เดือนงบประมาณ และอื่นๆ
หมายเหตุ: ฟังก์ชันวันที่และเวลาใน DAX ไม่เหมือนกับฟังก์ชันตัวแสดงเวลา เรียนรู้เพิ่มเติมเกี่ยวกับตัวแสดงเวลาใน Power Pivot Excel 2013
DAX มีฟังก์ชันวันที่และเวลาต่อไปนี้:
มีฟังก์ชัน DAX อื่นๆ อีกมากมายที่คุณสามารถใช้ในสูตรของคุณ ตัวอย่างเช่น สูตรหลายๆ สูตรที่อธิบายไว้ในที่นี้ใช้ฟังก์ชัน ทางคณิตศาสตร์และตรีโกณมิติ เช่นMOD และTRUNCฟังก์ชันทาง ตรรกะ เช่น ฟังก์ชันIFและฟังก์ชันข้อความ เช่นFORMATหากต้องการข้อมูลเพิ่มเติมเกี่ยวกับฟังก์ชัน DAX อื่นๆ ให้ดูที่ส่วน ทรัพยากรเพิ่มเติม ในบทความนี้
ตัวอย่างสูตรของปีปฏิทิน
ตัวอย่างต่อไปนี้อธิบายสูตรที่ใช้ในการสร้างคอลัมน์เพิ่มเติมในตารางวันที่ชื่อ ปฏิทิน หนึ่งคอลัมน์ มีชื่อ Date อยู่แล้ว และมีช่วงวันที่ต่อเนื่องกันตั้งแต่ 1/1/2010 ถึง 31/12/2016
ปี
=YEAR([date])
ในสูตรนี้ ฟังก์ชัน YEAR จะส่งกลับปีจากค่าในคอลัมน์ วันที่ เนื่องจากค่าในคอลัมน์ วันที่ เป็นชนิดข้อมูลวันที่เวลา ฟังก์ชัน YEAR จะทราบวิธีการส่งกลับปี
เดือน
=MONTH([date])
ในสูตรนี้เหมือนกับฟังก์ชัน YEAR เราเพียงแค่ใช้ฟังก์ชัน MONTH เพื่อส่งกลับค่าเดือนจากคอลัมน์ วันที่
ไตรมาส
=INT(([Month]+2)/3)
ในสูตรนี้ เราใช้ฟังก์ชัน INT เพื่อส่งกลับค่าวันที่เป็นจํานวนเต็ม อาร์กิวเมนต์ที่เราระบุให้กับฟังก์ชัน INT คือค่าจากคอลัมน์ เดือน เพิ่ม 2 แล้วหารด้วย 3 เพื่อให้ได้ค่าไตรมาสของเรา 1 ถึง 4
ชื่อเดือน
=FORMAT([date],"mmmm")
ในสูตรนี้ เมื่อต้องการรับชื่อเดือน เราจะใช้ฟังก์ชัน FORMAT เพื่อแปลงค่าตัวเลขจากคอลัมน์วันที่เป็นข้อความ เราระบุคอลัมน์วันที่เป็นอาร์กิวเมนต์แรก แล้วระบุรูปแบบ เราต้องการให้ชื่อเดือนของเราแสดงอักขระทั้งหมด เราจึงใช้ "mmmm" ผลลัพธ์ของเรามีลักษณะดังนี้:
ถ้าเราต้องการส่งกลับตัวอักษรย่อชื่อเดือนเป็นตัวอักษรสามตัว เราจะใช้ "mmm" ในอาร์กิวเมนต์รูปแบบ
วันของสัปดาห์
=FORMAT([date],"ddd")
ในสูตรนี้ เราจะใช้ฟังก์ชัน FORMAT เพื่อรับชื่อวัน เนื่องจากเราเพียงต้องการชื่อย่อวัน เราจึงระบุ "ddd" ในอาร์กิวเมนต์รูปแบบ
PivotTable ตัวอย่าง
เมื่อคุณมีเขตข้อมูลวันที่ เช่น ปี ไตรมาส เดือน เป็นต้น คุณสามารถใช้เขตข้อมูลเหล่านั้นใน PivotTable หรือรายงาน ตัวอย่างเช่น รูปต่อไปนี้แสดงเขตข้อมูล SalesAmount จากตาราง ข้อเท็จจริงเกี่ยวกับยอดขาย ใน VALUES และ Year และ Quarter จากตารางมิติปฏิทินใน ROWS SalesAmount จะถูกรวมไว้ตามบริบทของปีและไตรมาส
ตัวอย่างสูตรของปีงบประมาณ
ปีงบประมาณ
=IF([เดือน]<= 6,[ปี],[ปี]+1)
ในตัวอย่างนี้ ปีงบประมาณเริ่มต้นในวันที่ 1 กรกฎาคม
ไม่มีฟังก์ชันที่สามารถแยกปีงบประมาณออกจากค่าวันที่ เนื่องจากวันที่เริ่มต้นและสิ้นสุดของปีงบประมาณมักจะแตกต่างจากปีปฏิทิน เมื่อต้องการรับปีงบประมาณ อันดับแรก เราจะใช้ฟังก์ชัน IF เพื่อทดสอบว่าค่าของ เดือน น้อยกว่าหรือเท่ากับ 6 ในอาร์กิวเมนต์ที่สอง ถ้าค่าของ Month น้อยกว่าหรือเท่ากับ 6 จะส่งกลับค่าจากคอลัมน์ ปี ถ้าไม่ จะส่งกลับค่าจาก Year และเพิ่ม 1
อีกวิธีหนึ่งในการระบุค่าเดือนสิ้นสุดปีงบประมาณคือการสร้างการวัดที่ระบุแค่เดือน ตัวอย่างเช่น FYE:=6 จากนั้น คุณสามารถอ้างอิงชื่อการวัดโดยอ้างอิงตามหมายเลขเดือนได้ ตัวอย่างเช่น =IF([เดือน]<=[FYE],[ปี],[ปี]+1) ซึ่งให้ความยืดหยุ่นมากขึ้นเมื่ออ้างอิงเดือนสิ้นสุดปีงบประมาณในสูตรที่แตกต่างกันหลายสูตร
เดือนงบประมาณ
=IF([Month]<= 6, 6+[Month], [Month]- 6)
ในสูตรนี้ เราระบุถ้าค่าของ [เดือน] น้อยกว่าหรือเท่ากับ 6 แล้วรับ 6 และเพิ่มค่าจาก เดือน นอกเหนือจากนั้นให้ลบ 6 จากค่าจาก [เดือน]
ไตรมาสงบประมาณ
=INT(([FiscalMonth]+2)/3)
สูตรที่เราใช้กับ FiscalQuarter จะเหมือนกับของไตรมาสในปีปฏิทินของเรา ข้อแตกต่างเพียงข้อเดียวคือ เราระบุ [FiscalMonth] แทน [เดือน]
วันหยุดหรือวันที่พิเศษ
คุณอาจต้องการรวมคอลัมน์วันที่ที่ระบุวันที่บางรายการเป็นวันหยุดหรือวันที่พิเศษอื่นๆ ตัวอย่างเช่น คุณอาจต้องการรวมผลรวมยอดขายของวันปีใหม่โดยการเพิ่มเขตข้อมูลวันหยุดลงใน PivotTable ในฐานะตัวแบ่งส่วนข้อมูล หรือตัวกรอง ในกรณีอื่น คุณอาจต้องการแยกวันที่เหล่านั้นออกจากคอลัมน์วันที่อื่นหรือตามการวัด
รวมถึงวันหยุดหรือวันพิเศษที่ค่อนข้างง่าย คุณสามารถสร้างตารางใน Excelที่มีวันที่ที่คุณต้องการรวมไว้ จากนั้นคุณสามารถคัดลอกหรือใช้ เพิ่มลงในตัวแบบข้อมูล เพื่อเพิ่มลงในตัวแบบข้อมูลเป็นตารางที่ลิงก์ ในกรณีส่วนใหญ่ ไม่จําเป็นต้องสร้างความสัมพันธ์ระหว่างตารางและตารางปฏิทิน สูตรใดๆ ที่อ้างถึงสูตรสามารถใช้ฟังก์ชัน LOOKUPVALU1 เพื่อส่งกลับค่าได้
ด้านล่างนี้คือตัวอย่างของตารางที่สร้างขึ้นในExcelรวมวันหยุดที่จะเพิ่มลงในตารางวันที่:
|
วันที่ |
วันหยุด |
|---|---|
|
1/1/2010 |
ปีใหม่ |
|
11/25/2010 |
อวตาดี |
|
12/25/2010 |
คริสต์มาส |
|
1/1/2554 |
ปีใหม่ |
|
11/24/2011 |
อวตาดี |
|
12/25/2011 |
คริสต์มาส |
|
1/1/2555 |
ปีใหม่ |
|
22/11/2555 |
อวตาดี |
|
12/25/2012 |
คริสต์มาส |
|
1/1/2013 |
ปีใหม่ |
|
11/28/2013 |
อวตาดี |
|
12/25/2013 |
คริสต์มาส |
|
11/27/2014 |
อวตาดี |
|
12/25/2014 |
คริสต์มาส |
|
1/1/2557 |
ปีใหม่ |
|
11/27/2014 |
อวตาดี |
|
12/25/2014 |
คริสต์มาส |
|
1/1/2015 |
ปีใหม่ |
|
11/26/2014 |
อวตาดี |
|
12/25/2015 |
คริสต์มาส |
|
1/1/2559 |
ปีใหม่ |
|
11/24/2016 |
อวตาดี |
|
12/25/2016 |
คริสต์มาส |
ในตารางวันที่ เราจะสร้างคอลัมน์ชื่อ วันหยุด และใช้สูตรดังนี้
=LOOKUPVALU1(Holidays[Holiday],Holidays[date],Calendar[date])
มาดูสูตรนี้ให้ละเอียดมากขึ้น
เราใช้ฟังก์ชัน LOOKUPVALU1 เพื่อรับค่าจากคอลัมน์ วันหยุด ในตาราง วันหยุด ในอาร์กิวเมนต์แรก เราจะระบุคอลัมน์ที่ค่าผลลัพธ์ของเราจะเป็น เราระบุ คอลัมน์ วันหยุด ในตาราง วันหยุด เนื่องจากเป็นค่าที่เราต้องการส่งกลับ
=LOOKUPVALU1(วันหยุด[วันหยุด],วันหยุด[วันที่],ปฏิทิน[วันที่])
จากนั้นเราจะระบุอาร์กิวเมนต์ที่สอง คือคอลัมน์การค้นหาที่มีวันที่ที่เราต้องการค้นหา เราระบุ คอลัมน์ วันที่ ในตาราง วันหยุด ดังนี้
=LOOKUPVALU1(Holidays[Holiday],Holidays[date],Calendar[date])
สุดท้าย เราจะ ระบุคอลัมน์ในตาราง ปฏิทิน ของเราที่มีวันที่ที่เราต้องการค้นหา ในตาราง วันหยุด หลักสูตรนี้เป็น คอลัมน์ วันที่ ในตาราง ปฏิทิน
=LOOKUPVALU1(Holidays[Holiday],Holidays[date],Calendar[date])
คอลัมน์ วันหยุด จะส่งกลับชื่อวันหยุดของแต่ละแถวซึ่งมีค่าวันที่ตรงกับวันที่ในตาราง วันหยุด
ปฏิทินแบบปรับแต่งเอง - ช่วงเวลา 14 สัปดาห์ 14 สัปดาห์
บางองค์กร เช่น ร้านค้าปลีกหรือบริการอาหาร มักจะรายงานเกี่ยวกับช่วงเวลาที่แตกต่างกัน เช่น ช่วงเวลาสิบสี่สัปดาห์ ด้วยปฏิทินระยะเวลา 14 สัปดาห์ ระยะเวลาแต่ละช่วงคือ 28 วัน ดังนั้น แต่ละช่วงเวลาจะมีวันจันทร์สี่วัน สี่วันอังคาร สี่วันพุธ และอื่นๆ แต่ละช่วงเวลาจะมีจํานวนวันเหมือนกัน และโดยทั่วไปแล้ว วันหยุดจะอยู่ภายในช่วงเวลาเดียวกันในแต่ละปี คุณสามารถเลือกที่จะเริ่มต้นช่วงเวลาในวันใดก็ได้ของสัปดาห์ เช่นเดียวกับวันที่ในปฏิทินหรือปีงบประมาณ คุณสามารถใช้ DAX เพื่อสร้างคอลัมน์เพิ่มเติมที่มีวันที่แบบปรับแต่งเองได้
ในตัวอย่างด้านล่าง ช่วงเวลาแบบเต็มช่วงแรกจะเริ่มต้นในวันอาทิตย์แรกของปีงบประมาณ ในกรณีนี้ ปีงบประมาณเริ่มต้นในวันที่ 1/7
สัปดาห์
ค่านี้จะให้หมายเลขสัปดาห์ที่เริ่มต้นด้วยสัปดาห์เต็มสัปดาห์แรกในปีงบประมาณ ในตัวอย่างนี้ สัปดาห์แบบเต็มสัปดาห์แรกจะเริ่มต้นในวันอาทิตย์ ดังนั้นสัปดาห์เต็มสัปดาห์แรกในปีงบประมาณแรกในตาราง ปฏิทิน จะเริ่มต้นในวันที่ 4/7/2553 และต่อเนื่องจนถึงสัปดาห์สุดท้ายในตารางปฏิทิน ในขณะที่ค่านี้ไม่ใช่สิ่งที่มีประโยชน์ในการวิเคราะห์ แต่จําเป็นต้องคํานวณเพื่อใช้ในสูตรช่วงเวลา 28 วันอื่นๆ
=INT([date]-40356)/7)
มาดูสูตรนี้ให้ละเอียดมากขึ้น
ขั้นแรก เราจะสร้างสูตรที่ส่งกลับค่าจากคอลัมน์วันที่เป็นจํานวนเต็ม ดังนี้
=INT([date])
จากนั้น เราอยากค้นหาวันอาทิตย์แรกในปีงบประมาณแรก เราเห็นว่าเป็นวันที่ 4/7/2553
ในตอนนี้ ให้ลบ 40356 (ซึ่งเป็นจํานวนเต็ม 6/27/2010 วันอาทิตย์สุดท้ายจากปีงบประมาณก่อนหน้า) จากค่านั้นเพื่อให้ได้จํานวนวันตั้งแต่วันที่เริ่มต้นวันในตาราง ปฏิทิน ของเรา ดังนี้
=INT([date]-40356)
แล้วหารผลลัพธ์ด้วย 7 (วันในหนึ่งสัปดาห์) ดังนี้
=INT(([date]-40356)/7)
ผลลัพธ์จะมีลักษณะดังนี้:
เครื่องหมายมหัพภาค
ช่วงเวลาในปฏิทินแบบปรับแต่งเองนี้มี 28 วัน และจะเริ่มในวันอาทิตย์เสมอ คอลัมน์นี้จะส่งกลับจํานวนของคาบเวลาที่เริ่มต้นด้วยวันอาทิตย์แรกในปีงบประมาณแรก
=INT(([Week]+3)/4)
มาดูสูตรนี้ให้ละเอียดมากขึ้น
ขั้นแรก เราจะสร้างสูตรที่ส่งกลับค่าจากคอลัมน์ สัปดาห์ เป็นจํานวนเต็ม ดังนี้
=INT([Week])
แล้วบวก 3 ลงในค่านั้น ดังนี้
=INT([Week]+3)
แล้วหารผลลัพธ์ด้วย 4 ดังนี้
=INT(([Week]+3)/4)
ผลลัพธ์จะมีลักษณะดังนี้:
ปีงบประมาณของช่วงเวลา
ค่านี้จะส่งกลับปีงบประมาณของช่วงเวลาหนึ่ง
=INT(([Period]+12)/13)+2008
มาดูสูตรนี้ให้ละเอียดมากขึ้น
ก่อนอื่น เราจะสร้างสูตรที่ส่งกลับค่าจาก Period และเพิ่ม 12:
= ([Period]+12)
เราหารผลลัพธ์ด้วย 13 เนื่องจากมีช่วงเวลา 18 วันในช่วงปีงบประมาณ:
=(([Period]+12)/13)
เราได้เพิ่ม 2010 เนื่องจากเป็นปีแรกในตาราง:
=(([Period]+12)/13)+2010
สุดท้าย เราจะใช้ฟังก์ชัน INT เพื่อเอาเศษส่วนของผลลัพธ์ออก และส่งกลับจํานวนเต็ม เมื่อหารด้วย 13 ดังนี้:
=INT(([Period]+12)/13)+2010
ผลลัพธ์จะมีลักษณะดังนี้:
ช่วงเวลาในปีงบประมาณ
ค่านี้จะส่งกลับตัวเลขช่วงเวลา 1 – 13 เริ่มต้นจากคาบเวลาแบบเต็มช่วงแรก (เริ่มต้นจากวันอาทิตย์) ในแต่ละปีงบประมาณ
=IF(MOD([Period],13), MOD([Period],13),13)
สูตรนี้มีความซับซ้อนมากขึ้นเล็กน้อย ดังนั้นเราจะอธิบายเป็นภาษาที่เราเข้าใจได้ดีขึ้นก่อน สูตรนี้จะระบุ ให้หารค่าจาก [มหัพภาพ] ด้วย 13 เพื่อให้ได้เลขคาบเวลา (1-13) ในปี ถ้าตัวเลขเป็น 0 ให้ส่งกลับ 13
ก่อนอื่น เราจะสร้างสูตรที่ส่งกลับค่าที่เหลือจาก Period ขึ้น 13 เราสามารถใช้ฟังก์ชัน MOD (คณิตศาสตร์และตรีโกณมิติ) แบบนี้ได้:
=MOD([Period],13)
ซึ่งส่วนใหญ่แล้ว จะให้ผลลัพธ์ที่เราต้องการ ยกเว้นที่ที่ค่าของ ช่วงเวลา เป็น 0 เนื่องจากวันที่เหล่านั้นไม่ตกไปภายในปีงบประมาณแรก เช่น ในห้าวันแรกของตารางวันที่ปฏิทินตัวอย่างของเรา เราสามารถจัดการสิ่งนี้ด้วยฟังก์ชัน IF ในกรณีที่ผลลัพธ์เป็น 0 เราจะส่งกลับ 13 ดังนี้:
=IF(MOD([Period],13),MOD([Period],13),13)
ผลลัพธ์จะมีลักษณะดังนี้:
PivotTable ตัวอย่าง
รูปด้านล่างแสดง PivotTable ที่มีเขตข้อมูล SalesAmount จากตาราง ข้อเท็จจริงเกี่ยวกับยอดขาย ใน VALUES และ PeriodFiscalYear และเขตข้อมูล PeriodInFiscalYear จากตารางมิติวันที่ของปฏิทินใน ROWS SalesAmount จะถูกรวมไว้ด้วยบริบทตามปีงบประมาณ และช่วงเวลา 28 วันในปีงบประมาณ
ความสัมพันธ์
หลังจากที่คุณได้สร้างตารางวันที่ในรูปแบบข้อมูลของคุณแล้ว เมื่อต้องการเริ่มเรียกดูข้อมูลของคุณใน PivotTable และรายงาน และเมื่อต้องการรวมข้อมูลโดยยึดตามคอลัมน์ในตารางมิติวันที่ของคุณ คุณต้องสร้างความสัมพันธ์ระหว่างตารางข้อเท็จจริงกับข้อมูลธุรกรรมของคุณและตารางวันที่
เนื่องจากคุณต้องสร้างความสัมพันธ์โดยยึดตามวันที่ คุณต้องตรวจสอบให้แน่ใจว่าคุณได้สร้างความสัมพันธ์ระหว่างคอลัมน์ที่มีค่าเป็นชนิดข้อมูล วันที่เวลา (วันที่)
ทุกๆ ค่าวันที่ในตารางข้อเท็จจริง คอลัมน์การค้นหาที่เกี่ยวข้องในตารางวันที่ต้องมีค่าที่ตรงกัน ตัวอย่างเช่น แถว (ระเบียนธุรกรรม) ในตาราง ข้อเท็จจริงเกี่ยวกับยอดขาย ที่มีมูลค่า 15/8/2555 12:00 น. ในคอลัมน์ DateKey จะต้องมีค่าที่สอดคล้องกันในคอลัมน์ วันที่ ที่เกี่ยวข้องในตาราง วันที่ (ปฏิทิน) ที่มีชื่อ นี่คือเหตุผลสําคัญที่สุดข้อหนึ่งที่คุณต้องการให้คอลัมน์วันที่ของคุณในตารางวันที่มีช่วงวันที่ต่อเนื่องกันที่มีวันที่ที่เป็นไปได้ในตารางข้อเท็จจริงของคุณ
หมายเหตุ: ขณะที่คอลัมน์วันที่ในแต่ละตารางต้องเป็นชนิดข้อมูลเดียวกัน (วันที่) รูปแบบของแต่ละคอลัมน์จะไม่เกี่ยวกับเรื่องนี้
หมายเหตุ: ถ้า Power Pivot ไม่ให้คุณสร้างความสัมพันธ์ระหว่างสองตาราง เขตข้อมูลวันที่อาจไม่เก็บวันที่และเวลาไว้ในระดับความแม่นยาเดียวกัน ค่าอาจมีลักษณะเหมือนกัน แต่ถูกจัดเก็บแตกต่างกัน ทั้งนี้ขึ้นอยู่กับการจัดรูปแบบของคอลัมน์ อ่านเพิ่มเติมเกี่ยวกับการ ใช้งานได้กับเวลา
หมายเหตุ: หลีกเลี่ยงการใช้คีย์ตัวแทนจํานวนเต็มในความสัมพันธ์ เมื่อคุณนําเข้าข้อมูลจากแหล่งข้อมูลที่สัมพันธ์กัน คอลัมน์วันที่และเวลามักจะถูกแสดงด้วยคีย์ตัวแทน ซึ่งเป็นคอลัมน์จํานวนเต็มที่ใช้แทนวันที่ไม่ซ้ากัน ใน Power Pivot คุณควรหลีกเลี่ยงการสร้างความสัมพันธ์โดยใช้คีย์วันที่/เวลาแบบจํานวนเต็ม และใช้คอลัมน์ที่มีค่าเฉพาะที่มีชนิดข้อมูลวันที่แทน แม้ว่าการใช้คีย์ตัวแทนจะถือว่าเป็นแนวทางปฏิบัติที่ดีที่สุดในคลังสินค้าข้อมูลแบบดั้งเดิม แต่ไม่ต้องใช้คีย์จํานวนเต็มใน Power Pivot และอาจจะยากต่อการจัดกลุ่มค่าใน PivotTable ตามช่วงเวลาวันที่ที่แตกต่างกัน
ถ้าคุณได้รับข้อผิดพลาด ชนิด ไม่ตรงกัน เมื่อพยายามสร้างความสัมพันธ์ อาจเนื่องจากคอลัมน์ในตารางข้อเท็จจริงไม่ใช่ชนิดข้อมูลวันที่ ซึ่งอาจเกิดขึ้นได้เมื่อ Power Pivot ไม่สามารถแปลงวันที่ที่ไม่ใช่วันที่โดยอัตโนมัติ (โดยปกติคือชนิดข้อมูลข้อความ) เป็นชนิดข้อมูลวันที่ คุณยังคงสามารถใช้คอลัมน์ในตารางข้อเท็จจริงของคุณ แต่คุณจะต้องแปลงข้อมูลด้วยสูตร DAX ในคอลัมน์คํานวณใหม่ ดู การแปลงวันที่ชนิดข้อมูลข้อความเป็นชนิดข้อมูลวันที่ภายหลัง ในภาคผนวก
หลายความสัมพันธ์
ในบางกรณี อาจจําเป็นต้องสร้างความสัมพันธ์หลายรายการหรือสร้างตารางวันที่หลายตาราง ตัวอย่างเช่น ถ้ามีเขตข้อมูลวันที่หลายเขตข้อมูลในตาราง ข้อเท็จจริงเกี่ยวกับการขาย เช่น DateKey, ShipDate และ ReturnDate เขตข้อมูลเหล่านั้นทั้งหมดสามารถมีความสัมพันธ์กับเขตข้อมูล วันที่ ในตารางวันที่ในปฏิทิน แต่มีเพียงเขตข้อมูลเดียวที่สามารถเป็นความสัมพันธ์ที่ใช้งานอยู่ได้ ในกรณีนี้ เนื่องจาก DateKey แสดงถึงวันที่ของธุรกรรม ดังนั้นวันที่ที่สําคัญที่สุด การเช่นนี้จะเป็นความสัมพันธ์ที่ใช้งานอยู่ได้ดีที่สุด ผู้อื่นมีความสัมพันธ์ที่ไม่ได้ใช้งาน
PivotTable ต่อไปนี้จะคํานวณยอดขายรวมตาม ปีงบประมาณ และ ไตรมาสงบประมาณ การวัดที่ชื่อ Total Sales ด้วยสูตร Total Sales:=SUM([SalesAmount])จะถูกวางใน VALUES และเขตข้อมูล FiscalYear และ FiscalQuarter จากตารางวันที่ปฏิทินจะถูกวางใน ROWS
PivotTable ที่ตรงไปข้างหน้านี้ใช้งานได้อย่างถูกต้องเนื่องจากเราต้องการรวมยอดขายทั้งหมดของเราตาม วันที่ธุรกรรมใน DateKey การวัดยอดขายรวมของเราใช้วันที่ใน DateKey และถูกรวมตามปีงบประมาณและไตรมาสงบประมาณ เนื่องจากมีความสัมพันธ์ระหว่าง DateKey ในตารางยอดขายและคอลัมน์ วันที่ ในตารางวันที่ในปฏิทิน
ความสัมพันธ์ที่ไม่ได้ใช้งาน
แต่จะเกิดอะไรขึ้นถ้าเราต้องการรวมยอดขายรวมของเราไม่เรียงตามวันที่ธุรกรรม แต่เรียงตามวันที่จัดส่ง เราต้องการความสัมพันธ์ระหว่างคอลัมน์ ShipDate ในตาราง ยอดขาย และคอลัมน์ วันที่ ในตาราง ปฏิทิน ถ้าเราไม่สร้างความสัมพันธ์นั้น การรวมของเราจะยึดตามวันที่ธุรกรรมเสมอ อย่างไรก็ตาม เราสามารถมีความสัมพันธ์ได้หลายความสัมพันธ์ แม้ว่ามีเพียงความสัมพันธ์เดียวที่สามารถใช้งานอยู่ได้ และเนื่องจากวันที่ทรานแซคชันสําคัญที่สุด จึงมีความสัมพันธ์ที่ใช้งานได้กับตารางปฏิทิน
ในกรณีนี้ ShipDate มีความสัมพันธ์ที่ไม่ได้ใช้งาน ดังนั้นสูตรการวัดใดๆ ที่สร้างขึ้นเพื่อรวบรวมข้อมูลโดยยึดตามวันที่จัดส่งต้องระบุความสัมพันธ์ที่ไม่ได้ใช้งานโดยใช้ฟังก์ชันUSERELATIONSHIP
ตัวอย่างเช่น เนื่องจากมีความสัมพันธ์ที่ไม่ได้ใช้งานระหว่างคอลัมน์ วันที่จัดส่ง ในตาราง ยอดขาย และคอลัมน์ วันที่ ในตาราง ปฏิทิน เราจึงสามารถสร้างการวัดที่รวมยอดขายรวมตามวันที่จัดส่งได้ เราใช้สูตรนี้เพื่อระบุความสัมพันธ์ที่จะใช้:
Total Sales by Ship Date:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))
สูตรนี้เพียงแค่ระบุ: คํานวณผลรวมของ SalesAmount แต่กรองโดยใช้ความสัมพันธ์ระหว่างคอลัมน์ วันที่จัดส่ง ในตาราง ยอดขาย และคอลัมน์ วันที่ ในตาราง ปฏิทิน
ตอนนี้ ถ้าเราสร้าง PivotTable และใส่ ยอดขายรวม ตามการวัดวันที่จัดส่งในค่า และปีงบประมาณและไตรมาสงบประมาณในแถว เราจะเห็นผลรวมทั้งหมดเดียวกัน แต่จํานวนรวมอื่นๆ ทั้งหมดของปีงบประมาณและไตรมาสงบประมาณจะแตกต่างกันเนื่องจากจะยึดตามวันที่จัดส่งไม่ใช่วันที่ธุรกรรม
การใช้ความสัมพันธ์ที่ไม่ได้ใช้งานจะอนุญาตให้คุณใช้ตารางวันที่เพียงหนึ่งตารางได้ แต่จะต้องใช้การวัดใดๆ (เช่น ยอดขายรวมตามวันที่จัดส่ง) อ้างอิงความสัมพันธ์ที่ไม่ได้ใช้งานในสูตรของตารางนั้น มีอีกทางเลือกหนึ่งคือ ใช้ตารางวันที่หลายตาราง
ตารางวันที่หลายตาราง
อีกวิธีหนึ่งในการร่วมงานกับคอลัมน์วันที่หลายคอลัมน์ในตารางข้อเท็จจริงของคุณคือการสร้างตารางวันที่หลายตาราง และสร้างความสัมพันธ์ที่ใช้งานอยู่แยกกันระหว่างคอลัมน์เหล่านั้น ลองมาดูตัวอย่างตารางยอดขายของเราอีกครั้ง เรามีสามคอลัมน์ที่มีวันที่เราอาจต้องการรวบรวมข้อมูล:
-
DateKey ที่มีวันที่ของการขายของธุรกรรมแต่ละรายการ
-
วันที่จัดส่ง – ที่มีวันที่และเวลาที่สินค้าที่ขายได้ถูกจัดส่งไปยังลูกค้า
-
วันที่ส่งกลับ – พร้อมกับวันที่และเวลาเมื่อได้รับสินค้าอย่างน้อยหนึ่งรายการที่ถูกส่งกลับ
โปรดอย่าลืมว่าเขตข้อมูล DateKey ที่มีวันที่ทรานแซคชันนั้นสําคัญที่สุด เราจะรวมข้อมูลส่วนใหญ่ของเราโดยยึดตามวันที่เหล่านี้ ดังนั้น เราจะต้องการความสัมพันธ์ระหว่างวันที่และคอลัมน์ วันที่ ในตาราง ปฏิทิน ถ้าเราไม่ต้องการสร้างความสัมพันธ์ที่ไม่ได้ใช้งานระหว่างวันที่จัดส่งและวันที่ส่งสินค้ากับเขตข้อมูลวันที่ในตารางปฏิทิน ดังนั้น จึงต้องมีสูตรการวัดแบบพิเศษ เราสามารถสร้างตารางวันที่เพิ่มเติมให้กับวันที่จัดส่งและวันที่ส่งกลับได้ จากนั้นเราสามารถสร้างความสัมพันธ์ที่ใช้งานได้ระหว่างความสัมพันธ์เหล่านั้น
ในตัวอย่างนี้ เราได้สร้างตารางวันที่อื่นที่ชื่อ ShipCalendar แน่นอนว่า ยังหมายถึงการสร้างคอลัมน์วันที่เพิ่มเติม และเนื่องจากคอลัมน์วันที่เหล่านี้อยู่ในตารางวันที่อื่น เราต้องการตั้งชื่อคอลัมน์ในแบบที่แตกต่างจากคอลัมน์เดียวกันในตารางปฏิทิน ตัวอย่างเช่น เราได้สร้างคอลัมน์ที่ชื่อว่า ShipYear, ShipMonth, ShipQuarter และอื่นๆ
ถ้าเราสร้าง PivotTable และใส่การวัดยอดขายรวมใน VALUES และ ShipFiscalYear และ ShipFiscalQuarter ใน ROWS เราจะเห็นผลลัพธ์เดียวกันกับที่เราเห็นเมื่อเราสร้างความสัมพันธ์ที่ไม่ได้ใช้งานอยู่และยอดขายรวมพิเศษตามเขตข้อมูลการคํานวณวันที่จัดส่ง
แนวทางเหล่านี้แต่ละวิธีต้องมีการพิจารณาอย่างรอบคอบ เมื่อใช้หลายความสัมพันธ์กับตารางวันที่เดียว คุณอาจต้องสร้างการวัดพิเศษที่ความสัมพันธ์แบบไม่มีการใช้งานแบบส่งต่อโดยใช้ฟังก์ชัน USERELATIONSHIP ในอีกทางหนึ่ง การสร้างตารางวันที่หลายตารางอาจสร้างความสับสนในรายการเขตข้อมูลได้ และเนื่องจากมีตารางเพิ่มเติมในตัวแบบข้อมูล จึงจําเป็นต้องมีหน่วยความจํามากขึ้น ทดลองกับสิ่งที่เหมาะกับคุณที่สุด
คุณสมบัติตารางวันที่
คุณสมบัติ ตารางวันที่ จะตั้งค่าเมตาดาTime-Intelligenceจําเป็นเพื่อให้ฟังก์ชันต่างๆ เช่น TOTALYTD, PREVIOUSMONTH และ DATESBETWEEN สามารถพิมพ์ได้อย่างถูกต้อง เมื่อการคํานวณถูกเรียกใช้หนึ่งในฟังก์ชันเหล่านี้ กลไกจัดการสูตรของ Power Pivot จะทราบที่ที่จะไปรับวันที่ที่ต้องใช้
คำเตือน: ถ้าไม่ได้ตั้งค่าคุณสมบัตินี้ การวัดที่ใช้ฟังก์ชัน DAX Time-Intelligence อาจแสดงผลลัพธ์ไม่ถูกต้อง
เมื่อคุณตั้งค่าคุณสมบัติ ตารางวันที่ คุณจะระบุตารางวันที่และคอลัมน์วันที่ของชนิดข้อมูล Date (datetime)
วิธีการ: ตั้งค่าคุณสมบัติ ตารางวันที่
-
ในหน้าต่าง PowerPivotปฏิทิน ให้เลือกตารางปฏิทิน
-
บนแท็บ ออกแบบ ให้คลิก เครื่องหมายเป็นตารางวันที่
-
ในกล่องโต้ตอบ กาเครื่องหมายเป็นตารางวันที่ ให้เลือกคอลัมน์ที่มีค่าเฉพาะและชนิดข้อมูลวันที่
การที่ใช้งานได้กับเวลา
ค่าวันที่ทั้งหมดที่มีชนิดข้อมูลวันที่ExcelหรือSQL Serverเป็นตัวเลขจริงๆ รวมอยู่ในตัวเลขนั้นคือตัวเลขที่อ้างอิงถึงเวลา ในหลายกรณี เวลาที่แต่ละแถวและทุกแถวคือเที่ยงคืน ตัวอย่างเช่น ถ้าเขตข้อมูล DateTimeKey ในตารางข้อมูลการขายมีค่า เช่น 19/10/2553 12:00:00 น. แสดงว่าค่าดังกล่าวอยู่ในระดับวันที่มีความเที่ยงตรง ถ้าค่าเขตข้อมูล DateTimeKey มีเวลาที่รวมอยู่ ด้วย ตัวอย่างเช่น 19/10/2553 8:44:00 น. จะหมายความว่าค่าดังกล่าวจะมีระดับความเที่ยงตรงเป็นนาที ค่าอาจเป็นความเที่ยงตรงของระดับชั่วโมง หรือระดับความเที่ยงตรงเป็นวินาทีคู่ ระดับความแม่นยสําคัญในค่าเวลาจะมีผลกระทบอย่างมากต่อวิธีที่คุณสร้างตารางวันที่และความสัมพันธ์ระหว่างตารางข้อเท็จจริงของคุณ
คุณต้องตรวจสอบว่าคุณจะรวมข้อมูลของคุณไปยังระดับความแม่นยาของวันหรือตามระดับความแม่นยาของเวลา กล่าวคือ คุณอาจต้องการใช้คอลัมน์ในตารางวันที่ของคุณ เช่น เช้า บ่าย หรือ ชั่วโมง เป็นเขตข้อมูลวันที่เวลาในพื้นที่ แถว คอลัมน์ หรือ ตัวกรอง ของ PivotTable
หมายเหตุ: วัน คือหน่วยเวลาที่น้อยที่สุดที่ฟังก์ชันตัวแสดงเวลาของ DAX สามารถใช้งาน ถ้าคุณไม่ต้องการใช้ค่าเวลา คุณควรลดความแม่นยาของข้อมูลของคุณเพื่อใช้วันเป็นหน่วยต่สุด
ถ้าคุณต้องการรวมข้อมูลของคุณไปยังระดับเวลา ตารางวันที่ของคุณจะต้องมีคอลัมน์วันที่ที่รวมเวลาด้วย ที่จริงแล้ว คอลัมน์วันที่ต้องมีหนึ่งแถวในทุกๆ ชั่วโมง หรือแม้แต่ทุกนาทีของทุกวัน ทุกปีในช่วงวันที่ เนื่องจากการสร้างความสัมพันธ์ระหว่างคอลัมน์ DateTimeKey ในตารางข้อเท็จจริงและคอลัมน์วันที่ในตารางวันที่ คุณต้องมีค่าที่ตรงกัน ดังที่คุณสามารถจินตนาการได้ ถ้าคุณมีเวลาหลายปี สิ่งนี้สามารถมีไว้สร้างตารางวันที่ที่ใหญ่มากได้
ในกรณีส่วนใหญ่ คุณต้องการรวมข้อมูลของคุณในวันนั้นเท่านั้น กล่าวคือ คุณจะใช้คอลัมน์ เช่น ปีเดือนสัปดาห์ หรือ วันของสัปดาห์ เป็นเขตข้อมูลในพื้นที่ แถว คอลัมน์ หรือ ตัวกรอง ของ PivotTable ในกรณีนี้ คอลัมน์วันที่ในตารางวันที่ต้องมีแถวเพียงแถวเดียวในแต่ละวันในหนึ่งปี ตามที่เราได้อธิบายไว้ก่อนหน้านี้
ถ้าคอลัมน์วันที่ของคุณมีระดับเวลาทศนิยม แต่คุณจะรวมเฉพาะระดับวันเพื่อสร้างความสัมพันธ์ระหว่างตารางข้อเท็จจริงและตารางวันที่ คุณอาจต้องปรับเปลี่ยนตารางข้อเท็จจริงของคุณโดยการสร้างคอลัมน์ใหม่ที่ตัดทอนค่าในคอลัมน์วันที่เป็นค่าวัน หรืออีกนัยหนึ่ง ให้แปลงค่า เช่น 19/10/2553 8:44:00น . เป็น 19/10/2553 12:00:00 น. จากนั้น คุณสามารถสร้างความสัมพันธ์ระหว่างคอลัมน์ใหม่นี้กับคอลัมน์วันที่ในตารางวันที่ได้เนื่องจากค่าตรงกัน
มาดูตัวอย่างกัน รูปนี้แสดงคอลัมน์ DateTimeKey ในตาราง ข้อเท็จจริงเกี่ยวกับการขาย การรวมทั้งหมดของข้อมูลในตารางนี้จะต้องอยู่ในระดับวันเท่านั้น โดยใช้คอลัมน์ในตารางวันที่ในปฏิทิน เช่น ปี เดือน ไตรมาส เป็นต้น เวลาที่รวมอยู่ในค่าจะไม่เกี่ยวข้อง เฉพาะวันที่จริงเท่านั้น
เนื่องจากเราไม่ต้องการวิเคราะห์ข้อมูลนี้ในระดับเวลา เราจึงไม่ต้องใช้คอลัมน์ วันที่ ในตารางวันที่ของปฏิทินเพื่อรวมแถวหนึ่งแถวของทุกชั่วโมงและทุกนาทีของทุกวันในแต่ละปี ดังนั้น คอลัมน์วันที่ในตารางวันที่ของเราจะมีลักษณะดังนี้:
เมื่อต้องการสร้างความสัมพันธ์ระหว่างคอลัมน์ DateTimeKey ในตาราง Sales และคอลัมน์ Date ในตารางปฏิทิน เราสามารถสร้างคอลัมน์ใหม่ที่ถูกคํานวณในตาราง Sales Fact และใช้ฟังก์ชัน TRUNC เพื่อตัดทอนค่าวันที่และเวลาในคอลัมน์ DateTimeKey เป็นค่าวันที่ที่ตรงกับค่าในคอลัมน์ วันที่ ในตาราง ปฏิทิน สูตรของเรามีลักษณะดังนี้:
=TRUNC([DateTimeKey],0)
ซึ่งจะช่วยให้เราสร้างคอลัมน์ใหม่ (เราตั้งชื่อว่า DateKey) พร้อมวันที่จากคอลัมน์ DateTimeKey และเวลา 12:00:00 น. ในแต่ละแถว:
ตอนนี้เราสามารถสร้างความสัมพันธ์ระหว่างคอลัมน์ใหม่นี้ (DateKey) และคอลัมน์ วันที่ ในตารางปฏิทินได้
ในเดียวกัน เราสามารถสร้างคอลัมน์ที่คํานวณในตารางยอดขายที่ลดความเที่ยงตรงของเวลาในคอลัมน์ DateTimeKey เป็นระดับชั่วโมงความเที่ยงตรง ในกรณีนี้ ฟังก์ชัน TRUNC จะใช้งานไม่ได้ แต่เรายังคงสามารถใช้ ฟังก์ชันวันที่และเวลาของ DAX อื่นๆ เพื่อแยกและต่อค่าใหม่ไปยังระดับชั่วโมงที่มีความเที่ยงตรง เราสามารถใช้สูตรดังนี้:
= DATE (YEAR([DateTimeKey]), MONTH([DateTimeKey]), DAY([DateTimeKey]) ) + TIME (HOUR([DateTimeKey]), 0, 0)
คอลัมน์ใหม่ของเรามีลักษณะดังนี้:
ถ้าคอลัมน์ วันที่ ในตารางวันที่มีค่าจนถึงระดับชั่วโมงความเที่ยงตรง เราสามารถสร้างความสัมพันธ์ระหว่างคอลัมน์เหล่านั้นได้
การสร้างวันที่ให้ใช้งานมากขึ้น
คอลัมน์วันที่หลายคอลัมน์ที่คุณสร้างในตารางวันที่ของคุณเป็นสิ่งจําเป็นต่อเขตข้อมูลอื่นๆ แต่ไม่ใช่ทั้งหมดที่เป็นประโยชน์ในการวิเคราะห์ ตัวอย่างเช่น เขตข้อมูล DateKey ในตารางยอดขายที่เราได้อ้างอิงและแสดงในบทความนี้เป็นเรื่องสําคัญ เนื่องจากธุรกรรมทุกรายการ ทรานแซคชันนั้นจะถูกบันทึกตามที่เกิดขึ้นในวันที่และเวลาที่เฉพาะเจาะจง แต่จากการวิเคราะห์และจุดรายงานของมุมมอง ไม่ใช่ทั้งหมดที่เป็นประโยชน์เนื่องจากเราไม่สามารถใช้เป็นแถว คอลัมน์ หรือเขตข้อมูลตัวกรองในตารางหรือรายงาน Pivot ได้
ในตัวอย่างของเรา คอลัมน์ วันที่ ในตาราง ปฏิทิน จะมีประโยชน์มาก และมีความร้ายแรงอย่างมาก แต่คุณไม่สามารถใช้คอลัมน์เป็นมิติใน PivotTable ได้
เมื่อต้องการเก็บตารางและคอลัมน์ให้เป็นประโยชน์ที่สุด และเมื่อต้องการสร้างรายงาน PivotTable หรือ Power View รายการเขตข้อมูลให้ง่ายต่อการนําทาง คุณจําเป็นต้องซ่อนคอลัมน์ที่ไม่จําเป็นจากเครื่องมือไคลเอ็นต์ คุณอาจต้องการซ่อนบางตารางด้วย ตารางวันหยุดที่แสดงก่อนหน้านี้ประกอบด้วยวันที่วันหยุดที่สําคัญในบางคอลัมน์ในตารางปฏิทิน แต่คุณไม่สามารถใช้คอลัมน์ วันที่ และ วันหยุด ในตาราง วันหยุด เป็นเขตข้อมูลใน PivotTable ได้ ที่นี่อีกครั้ง เมื่อต้องการให้รายการเขตข้อมูลนําทางได้ง่ายขึ้น คุณสามารถซ่อนตารางวันหยุดทั้งหมดได้
ส่วนสําคัญอีกอย่างหนึ่งของการใช้งานได้กับวันที่คือการตั้งชื่อแบบแผนการตั้งชื่อ คุณสามารถตั้งชื่อตารางและคอลัมน์ใน Power Pivot ตามที่คุณต้องการได้ แต่ควรระลึกไว้เสมอ โดยเฉพาะอย่างยิ่งถ้าคุณจะแชร์เวิร์กบุ๊กกับผู้ใช้อื่น การตั้งชื่อแบบแผนที่ดีจะช่วยให้การระบุตารางและวันที่ง่ายขึ้น ทั้งในรายการเขตข้อมูล และใน Power Pivot และในสูตร DAX ด้วย
หลังจากที่คุณมีตารางวันที่ในรูปแบบข้อมูลของคุณแล้ว คุณสามารถเริ่มสร้างการวัดที่จะช่วยให้คุณได้รับประโยชน์สูงสุดจากข้อมูลของคุณ บางรายการอาจง่ายเหมือนกับการรวมยอดขายรวมของปีปัจจุบัน และยอดขายอื่นๆ อาจมีความซับซ้อนมากขึ้น ซึ่งคุณต้องการกรองช่วงของวันที่เฉพาะบางช่วง เรียนรู้เพิ่มเติมใน การวัดในฟังก์ชัน Power Pivotและตัวแสดงเวลา
ภาคผนวก
การแปลงวันที่ของชนิดข้อมูลข้อความเป็นชนิดข้อมูลวันที่
ในบางกรณี ตารางข้อเท็จจริงที่มีข้อมูลธุรกรรมอาจมีวันที่ของชนิดข้อมูลเป็นข้อความ นั่นคือ วันที่ที่ปรากฏเป็น 2012-12-04T11:47:09 ที่จริงแล้วไม่ใช่วันที่เลย หรืออย่างน้อยก็ไม่ใช่ชนิดของวันที่ที่ Power Pivot สามารถเข้าใจได้ จริงๆ แล้วนี่เป็นเพียงข้อความที่อ่านเหมือนกับวันที่ เมื่อต้องการสร้างความสัมพันธ์ระหว่างคอลัมน์วันที่ในตารางข้อเท็จจริง และคอลัมน์วันที่ในตารางวันที่ ทั้งสองคอลัมน์ต้องเป็นชนิดข้อมูลวันที่
โดยทั่วไปแล้ว เมื่อคุณพยายามเปลี่ยนชนิดข้อมูลของคอลัมน์ของวันที่ที่เป็นชนิดข้อมูลข้อความเป็นชนิดข้อมูลวันที่ Power Pivot สามารถแปลวันที่และแปลงเป็นชนิดข้อมูลวันที่จริงโดยอัตโนมัติ ถ้า Power Pivot ไม่สามารถแปลงชนิดข้อมูลได้ คุณจะได้รับข้อผิดพลาดชนิดที่ไม่ตรงกัน
อย่างไรก็ตาม คุณยังสามารถแปลงวันที่เป็นชนิดข้อมูลวันที่จริงได้ คุณสามารถสร้างคอลัมน์จากการคํานวณใหม่และใช้สูตร DAX เพื่อแยกวิเคราะห์ปี เดือน วัน เวลา และอื่นๆ จากสตริงข้อความ แล้วเชื่อมเข้าด้วยกันอีกครั้งเพื่อให้ Power Pivot สามารถอ่านเป็นวันที่จริงได้
ในตัวอย่างนี้ เราได้นําเข้าตารางข้อเท็จจริงที่ชื่อว่า ยอดขาย ลงใน Power Pivot ซึ่งประกอบด้วยคอลัมน์ชื่อ DateTime ค่าที่ปรากฏดังนี้
ถ้าเราดูที่ชนิดข้อมูลในกลุ่มการจัดรูปแบบแท็บหน้าแรกของ Power Pivot เราจะเห็นว่าเป็นชนิดข้อมูลข้อความ
เราไม่สามารถสร้างความสัมพันธ์ระหว่างคอลัมน์ DateTime และคอลัมน์ วันที่ ในตารางวันที่ของเราได้เนื่องจากชนิดข้อมูลไม่ตรงกัน ถ้าเราพยายามเปลี่ยนชนิดข้อมูลเป็น วันที่เราได้รับข้อผิดพลาดชนิดที่ไม่ตรงกัน:
ในกรณีนี้ Power Pivot ไม่สามารถแปลงชนิดข้อมูลจากข้อความเป็นวันที่ได้ เรายังคงสามารถใช้คอลัมน์นี้ แต่เมื่อต้องการให้คอลัมน์เป็นชนิดข้อมูลวันที่จริง เราต้องสร้างคอลัมน์ใหม่ที่วิเคราะห์ข้อความแล้วสร้างใหม่ลงในค่า Power Pivot สามารถสร้างชนิดข้อมูลวันที่ได้
โปรดอย่าลืมว่า จากส่วน การพร้อมกับเวลา ในบทความนี้ เว้นแต่ว่าการวิเคราะห์ของคุณจําเป็นต้องอยู่ในระดับความแม่นยาของเวลาในแต่ละวัน คุณควรแปลงวันที่ในตารางข้อเท็จจริงของคุณให้เป็นระดับความแม่นยาในแต่ละวัน เมื่อทราบแล้ว เราต้องการให้ค่าในคอลัมน์ใหม่ของเราอยู่ที่ระดับวันที่มีความเที่ยงตรง (ยกเว้นเวลา) เราสามารถแปลงค่าในคอลัมน์ DateTime เป็นชนิดข้อมูลวันที่ และเอาระดับความเที่ยงตรงของเวลาออกด้วยสูตรต่อไปนี้:
=DATE(LEFT([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2))
ซึ่งจะช่วยให้เราสร้างคอลัมน์ใหม่ (ในกรณีนี้คือชื่อวันที่) Power Pivot ยังตรวจหาค่าเป็นวันที่ และตั้งค่าชนิดข้อมูลเป็น วันที่ โดยอัตโนมัติอีกด้วย
ถ้าเราต้องการรักษาระดับเวลาความเที่ยงตรง เราแค่ขยายสูตรให้รวมชั่วโมง นาที และวินาที
=DATE(LEFT([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2)) +
TIME(MID([DateTime],12,2), MID([DateTime],15,2), MID([DateTime],18,2))
ในตอนนี้เรามีคอลัมน์วันที่ของชนิดข้อมูลวันที่ เราสามารถสร้างความสัมพันธ์ระหว่างคอลัมน์และคอลัมน์วันที่ในวันที่ได้
แหล่งข้อมูลเพิ่มเติม
การเริ่มต้นใช้งานด่วนt: เรียนรู้ข้อมูลพื้นฐานเกี่ยวกับ DAX ภายใน 30 นาที
