טבלאות תאריכים ב- Power Pivot הן חיוניות לגלישה ולחישוב נתונים לאורך זמן. מאמר זה מספק הבנה מעמיקה של טבלאות תאריך וכיצד ניתן ליצור אותן ב- Power Pivot. באופן ספציפי, מאמר זה מתאר:
-
מדוע טבלת תאריכים חשובה לגלישה וחישוב של נתונים לפי תאריכים ושעה.
-
כיצד להשתמש ב- Power Pivot כדי להוסיף טבלת תאריכים למודל הנתונים.
-
כיצד ליצור עמודות תאריך חדשות כגון 'שנה', 'חודש' ו'תקופה' בטבלת תאריכים.
-
כיצד ליצור קשרי גומלין בין טבלאות תאריכים וטבלאות עובדה.
-
כיצד לעבוד עם זמן.
מאמר זה מיועד למשתמשים חדשים ב- Power Pivot. עם זאת, חשוב כבר להבין טוב את ייבוא הנתונים, יצירת קשרי גומלין ויצירת עמודות ומידות מחושבות.
מאמר זה אינו מתאר כיצד להשתמש בפונקציות DAX Time-Intelligence בנוסחאות מידה. לקבלת מידע נוסף אודות אופן יצירת מדידים באמצעות פונקציות DAX Time Intelligence, ראה בינת זמן ב- Power Pivot ב- Excel.
הערה: ב- Power Pivot, השמות "measure" ו- "calculated field" הם מילים נרדפות. אנחנו משתמשים במדידת השם לאורך מאמר זה. לקבלת מידע נוסף, ראה מדידים ב- Power Pivot.
תוכן
הכרת טבלאות תאריך
כמעט כל ניתוח הנתונים כולל גלישה והשוואה של נתונים לאורך תאריכים ושעה. לדוגמה, ייתכן שתרצה לסכם סכומי מכירות עבור רבעון הכספים הקודם, ולאחר מכן להשוות סכומים אלה לרבעונות אחרים, או לחשב יתרת סיום של חודש עבור חשבון. בכל אחד מהמקרים האלה, אתה משתמש בתאריכים כדרך לקבץ ולצבר עסקאות מכירה או יתרות עבור תקופה מסוימת בזמן.
דוח Power View
טבלת תאריכים יכולה להכיל ייצוגים שונים רבים של תאריכים ושעה. לדוגמה, טבלת תאריכים תכלול לעתים קרובות עמודות כגון שנת כספים, חודש, רבעון או תקופה שבאפשרותך לבחור כשדות מרשימת שדות בעת שכפול וסינון של הנתונים בטבלאות PivotTable או בדוחות Power View.
רשימת שדות Power View
כדי שעמודות תאריך כגון 'שנה', 'חודש' ו'רבעון' יכללו את כל התאריכים בטווח המתאים, טבלת התאריכים חייבת לכלול עמודה אחת לפחות עם ערכה רציפה של תאריכים. לדוגמה, עמודה זו חייבת לכלול שורה אחת עבור כל יום עבור כל שנה בטבלת התאריכים.
לדוגמה, אם הנתונים שברצונך לעיין בהם קיימים תאריכים מה- 1 בפברואר 2010 עד 30 בנובמבר 2012, ואתה מדווח על שנה קלנדרית, תרצה טבלת תאריכים עם טווח תאריכים לפחות מ- 1 בינואר 2010 עד 31 בדצמבר 2012. כל שנה בטבלת התאריכים שלך חייבת להכיל את כל הימים עבור כל שנה. אם אתה מרענן את הנתונים שלך באופן קבוע עם נתונים חדשים יותר, ייתכן שתרצה להפעיל את תאריך הסיום לפי שנה או שנתיים, כך שלא תצטרך לעדכן את טבלת התאריכים עם הזמן.
טבלת תאריכים עם ערכה רציפה של תאריכים
אם אתה מדווח על שנת כספים, באפשרותך ליצור טבלת תאריכים עם ערכה רציפה של תאריכים עבור כל שנת כספים. לדוגמה, אם שנת הכספים שלך מתחילה ב- 1 במרץ, ויש לך נתונים עבור שנות כספים 2010 עד התאריך הנוכחי (לדוגמה, ב- FY 2013), באפשרותך ליצור טבלת תאריכים המתחילה ב- 1/3/2009 וכוללת לפחות כל יום בכל שנת כספים עד התאריך האחרון בשנת 2013.
אם תדווח הן על שנה קלנדרית והן על שנת כספים, אין צורך ליצור טבלאות תאריכים נפרדות. טבלת תאריכים אחת יכולה לכלול עמודות עבור שנה קלנדרית, שנת כספים ואפילו לוח שנה של 13 שבועות לתקופה של ארבעה שבועות. הדבר החשוב הוא שטבלת התאריכים שלך מכילה ערכה רציפה של תאריכים עבור כל השנים הכלולות.
הוספת טבלת תאריכים למודל הנתונים
קיימות כמה דרכים להוספת טבלת תאריכים למודל הנתונים שלך:
-
יבא ממסד נתונים יחסי או ממקור נתונים אחר.
-
Create טבלת תאריכים ב- Excel ולאחר מכן העתק או קשר לטבלה חדשה ב- Power Pivot.
-
ייבוא מ Microsoft Azure Marketplace.
בוא נבחן כל אחד מהם בק דומה יותר.
ייבוא ממסד נתונים יחסי
אם אתה מייבא חלק מהנתונים שלך או את כולם ממחסן נתונים או סוג אחר של מסד נתונים יחסי, רוב הסיכויים שכבר קיימים טבלת תאריכים וקשרי גומלין ביניהם לבין שאר הנתונים שאתה מייבא. סביר להניח שהתאריכים והתבנית יתאימו לתאריכים בעובדה שלך, וסביר להניח שהתאריכים יתחילו היטב בעבר ויעבדו הרבה בעתיד. טבלת התאריכים שברצונך לייבא עשויה להיות גדולה מאוד ולהכיל טווח תאריכים מעבר למה שתצטרך לכלול במודל הנתונים שלך. באפשרותך להשתמש בתכונות הסינון המתקדמות של אשף ייבוא הטבלאות של Power Pivot כדי לבחור באופן סלקטיבי רק את התאריכים ואת העמודות הספציפיות הדרושות לך באמת. פעולה זו עשויה להקטין באופן משמעותי את גודל חוברת העבודה ולשפר את הביצועים.
אשף ייבוא הטבלאות
ברוב המקרים, לא תצטרך ליצור עמודות נוספות כגון 'שנת כספים', 'שבוע', 'שם חודש' וכן הלאה, מכיוון שהן כבר קיימות בטבלה המיובאת. עם זאת, במקרים מסוימים, לאחר ייבוא טבלת התאריכים למודל הנתונים שלך, ייתכן שיהיה עליך ליצור עמודות תאריך נוספות, בהתאם לצורך דיווח מסוים. למרבה המזל, קל לעשות זאת באמצעות DAX. תקבל מידע נוסף על יצירת שדות טבלת תאריכים מאוחר יותר. כל סביבה שונה. אם אינך בטוח אם למקורות הנתונים שלך יש תאריך או טבלת לוח שנה קשורים, פנה למנהל מסד הנתונים.
Create טבלת תאריכים ב- Excel
באפשרותך ליצור טבלת תאריכים ב- Excel ולאחר מכן להעתיק אותה לטבלה חדשה במודל הנתונים. פעולה זו מאוד קלה לביצוע והיא מעניקה לך גמישות רבה.
בעת יצירת טבלת תאריכים ב- Excel, אתה מתחיל בעמודה אחת עם טווח תאריכים רציף. לאחר מכן, באפשרותך ליצור עמודות נוספות כגון שנה, רבעון, חודש, שנת כספים, תקופה וכן הלאה בגליון העבודה של Excel באמצעות נוסחאות Excel, או לאחר העתקת הטבלה למודל הנתונים, באפשרותך ליצור אותן כעמודות מחושבות. יצירת עמודות תאריך נוספות ב- Power Pivot מתוארת בסעיף הוספת עמודות תאריך חדש לטבלת תאריכים בהמשך מאמר זה.
כיצד לבצע: Create טבלת תאריכים ב- Excel והעתק אותה למודל הנתונים
-
ב- Excel, בגליון עבודה ריק, בתא A1, הקלד שם כותרת עמודה כדי לזהות טווח תאריכים. בדרך כלל, פעולה זו תהיהדומה לתאריך, לתאריך ושעה או ל- DateKey.
-
בתא A2, הקלד תאריך התחלה. לדוגמה, 1/1/2010.
-
לחץ על נקודת האחיזה למילוי וגרור אותה כלפי מטה למספר שורה הכולל תאריך סיום. לדוגמה, 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 משתמשות בסוג נתונים של תאריך/שעה.
-
הם יכולים לקבל ערכים מעמודה כארגומנט.
-
ניתן להשתמש בהם כדי להחזיר ו/או לטפל בערכי תאריכים.
פונקציות אלה משמשות לעתים קרובות בעת יצירת עמודות תאריך מותאמות אישית בטבלת תאריכים, כך שחשוב להבין בהן. נשתמש במספר פונקציות אלה כדי ליצור עמודות עבור Year, Quarter, FiscalMonth וכן הלאה.
הערה: פונקציות תאריך ושעה ב- 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 הוא הערך מהעמודה Month, הוסף 2 ולאחר מכן חלק אותו ב- 3 כדי לקבל את הרבעון שלנו, 1 עד 4.
שם חודש
=FORMAT([date],"mmmm")
בנוסחה זו, כדי לקבל את שם החודש, אנו משתמשים בפונקציה FORMAT כדי להמיר ערך מספרי מהעמודה 'תאריך' לטקסט. אנו מציינים את העמודה Date כארגומנט הראשון ולאחר מכן את התבנית; אנחנו רוצים להוסיף את שם החודש שלנו להצגת כל התווים, ולכן אנו משתמשים ב- "mmmm". התוצאה שלנו נראית כך:
אם ברצוננו להחזיר את שם החודש המקוצר לשלוש אותיות, נשתמש ב- "mmm" בארגומנט התבנית.
יום בשבוע
=FORMAT([date],"ddd")
בנוסחה זו, אנו משתמשים בפונקציה FORMAT כדי לקבל את שם היום. מכיוון שאנחנו רוצים רק שם יום מקוצר, אנו מציינים "ddd" בארגומנט התבנית.
PivotTable לדוגמה
לאחר שיש לך שדות עבור תאריכים כגון 'שנה', 'רבעון', 'חודש' וכן הלאה, תוכל להשתמש בהם ב- PivotTable או בדוח. לדוגמה, התמונה הבאה מציגה את השדה SalesAmount מהטבלה Sales Fact ב- VALUES ו- Year ו- Quarter מהטבלה Calendar dimension ב- ROWS. SalesAmount נצבר עבור הקשר שנה ורבעון.
דוגמאות לנוסחאות עבור שנת כספים
שנת כספים
=IF([Month]<= 6,[Year],[Year]+1)
בדוגמה זו, שנת הכספים מתחילה ב- 1 ביולי.
אין פונקציה הגורמת לחילוץ שנת כספים מערך תאריך מאחר שתאריכי ההתחלה והסיום של שנת כספים שונים לעתים קרובות מאלה של שנה קלנדרית. כדי לקבל את שנת הכספים, נשתמש תחילה בפונקציה IF כדי לבדוק אם הערך עבור Month קטן או שווה ל- 6. בארגומנט השני, אם הערך עבור Month קטן או שווה ל- 6, החזר את הערך מהעמודה Year. אם לא, החזר את הערך מ- Year והוסף 1.
דרך נוספת לציין ערך של חודש סיום שנת כספים היא ליצור מידה שמציין את החודש בלבד. לדוגמה, FYE:=6. לאחר מכן תוכל להפנות אל שם המידה במקום מספר החודש. לדוגמה, =IF([Month]<=[FYE],[Year],[Year]+1). הדבר מספק גמישות רבה יותר בעת הפניה לחודש הסיום של שנת הכספים בכמה נוסחאות שונות.
חודש כספים
=IF([Month]<= 6, 6+[Month], [Month]- 6)
בנוסחה זו, אנו מציינים אם הערך עבור [Month] קטן או שווה ל- 6, לאחר מכן קח 6 והוסף את הערך מהחודש, אחרת החסר את 6 מהערך מ- [Month].
רבעון כספים
=INT(([FiscalMonth]+2)/3)
הנוסחה שבה אנו משתמשים עבור FiscalQuarter זהה לא פחות לרבעון בשנה קלנדרית שלנו. ההבדל היחיד הוא שאנו מציינים את [FiscalMonth] במקום [Month].
חגים או תאריכים מיוחדים
ייתכן שתרצה לכלול עמודת תאריך המציינת שתאריכים מסוימים הם חגים או תאריך מיוחד אחר. לדוגמה, ייתכן שתרצה לסכם סכומי מכירות עבור יום השנים החדשות על-ידי הוספת שדה חג ל- PivotTable, ככלי פריסה או כמסנן. במקרים אחרים, ייתכן שתרצה לא לכלול תאריכים אלה בעמודות תאריך אחרות או מדיד.
הכללת חגים או ימים מיוחדים היא פשוטה למדי. באפשרותך ליצור טבלה ב- Excel המכילה את התאריכים שברצונך לכלול. לאחר מכן תוכל להעתיק את מודל הנתונים או להשתמש בו כדי להוסיף אותו למודל הנתונים כטבלה מקושרת. ברוב המקרים, אין צורך ליצור קשר גומלין בין הטבלה לבין הטבלה לוח שנה. כל הנוסחאות המפנות אליהן יכולות להשתמש בפונקציה LOOKUPVALUE כדי להחזיר ערכים.
להלן דוגמה לטבלה שנוצרה ב- Excel הכוללת חגים להוספה לטבלת התאריכים:
|
תאריך |
חופש |
|---|---|
|
1/1/2010 |
שנים חדשות |
|
11/25/2010 |
חג ההודיה |
|
12/25/2010 |
חג המולד |
|
01/01/11 |
שנים חדשות |
|
11/24/2011 |
חג ההודיה |
|
12/25/2011 |
חג המולד |
|
01/01/12 |
שנים חדשות |
|
22/11/12 |
חג ההודיה |
|
12/25/2012 |
חג המולד |
|
1/1/2013 |
שנים חדשות |
|
11/28/2013 |
חג ההודיה |
|
12/25/2013 |
חג המולד |
|
11/27/2014 |
חג ההודיה |
|
12/25/2014 |
חג המולד |
|
01/01/2014 |
שנים חדשות |
|
11/27/2014 |
חג ההודיה |
|
12/25/2014 |
חג המולד |
|
1/1/2015 |
שנים חדשות |
|
11/26/2014 |
חג ההודיה |
|
12/25/2015 |
חג המולד |
|
01/01/16 |
שנים חדשות |
|
11/24/2016 |
חג ההודיה |
|
12/25/2016 |
חג המולד |
בטבלת התאריכים, אנו יוצרים עמודה בשם חג ונשתמש בנוסחה כגון זו:
=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])
נבחן נוסחה זו ביתר זהירות.
אנו משתמשים בפונקציה LOOKUPVALUE כדי לקבל ערכים מהעמודה 'חגים' בטבלה Holidays. בארגומנט הראשון, אנו מציינים את העמודה שבה יהיה ערך התוצאה שלנו. אנו מציינים את העמודה 'חגים' בטבלה ' חגים' מאחר שזהו הערך הרצוי.
=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])
לאחר מכן נציין את הארגומנט השני, עמודת החיפוש המכילה את התאריכים שברצונך לחפש. אנו מציינים את העמודה תאריך בטבלה Holidays , כך:
=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])
לבסוף, אנו מציינים את העמודה בטבלת לוח השנה המכילה את התאריכים שברצונך לחפש בטבלת החגים . כמובן, זוהי העמודה 'תאריך ' בטבלה ' לוח שנה '.
=LOOKUPVALUE(Holidays[Holiday],Holidays[date],Calendar[date])
העמודה חגים תחזיר את שם החג עבור כל שורה המכילה ערך תאריך התואם לתאריך בטבלה Holidays.
לוח שנה מותאם אישית - שלוש עשרה תקופות של ארבעה שבועות
ארגונים מסוימים, כגון שירות קמעונאות או מזון, מדווחים לעתים קרובות על תקופות שונות, כגון 13 תקופות של ארבעה שבועות. עם לוח שנה של 13 שבועות של ארבעה שבועות, כל תקופה היא 28 ימים; לכן, כל תקופה מכילה ארבעה ימי שני, ארבעה ימי שלישי, ארבעה ימי רביעי וכן הלאה. כל תקופה מכילה את אותו מספר ימים, ובדרך כלל, החגים יהיו באותה תקופה בכל שנה. באפשרותך לבחור להתחיל תקופה בכל יום בשבוע. בדיוק כמו עם תאריכים בלוח שנה או בשנת כספים, באפשרותך להשתמש ב- DAX כדי ליצור עמודות נוספות עם תאריכים מותאמים אישית.
בדוגמאות שלהלן, התקופה המלאה הראשונה מתחילה ביום ראשון הראשון של שנת הכספים. במקרה זה, שנת הכספים מתחילה ב- 1/7.
שבוע
ערך זה מספק לנו את מספר השבוע החל מהשבוע המלא הראשון בשנת הכספים. בדוגמה זו, השבוע המלא הראשון מתחיל ביום ראשון, כך שהשבוע המלא הראשון בשנת הכספים הראשונה בטבלה 'לוח שנה' מתחיל בפועל ב- 4/7/2010 וממשיך בשבוע המלא האחרון בטבלה 'לוח שנה'. למרות שערך זה עצמו אינו כל כך שימושי בניתוח, יש צורך לחשב לשימוש בנוסחאות אחרות של 28 ימים.
=INT([date]-40356)/7)
נבחן נוסחה זו ביתר זהירות.
תחילה, אנו יוצרים נוסחה המחזירה ערכים מהעמודה 'תאריך' כמספר שלם, כך:
=INT([date])
לאחר מכן אנחנו רוצים לחפש את יום ראשון הראשון בשנת הכספים הראשונה. אנחנו רואים שזה 7/4/2010.
כעת, החסר את 40356 (שהוא המספר השלם עבור 27/6/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
נבחן נוסחה זו ביתר זהירות.
תחילה, אנו יוצרים נוסחה המחזירה ערך מנקודה ומוסיף 12:
= ([Period]+12)
אנו מחלקים את התוצאה ב- 13, מכיוון שיש שלוש עשרה תקופות של 28 ימים בשנת הכספים:
=(([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.
תחילה, אנו יוצרים נוסחה המחזירה את שארית הערך מנקודה ב- 13. ניתן להשתמש בפונקציות MOD (פונקציות מתמטיות וטריגונומטריות) באופן הבא:
=MOD([Period],13)
הדבר, ברוב הימים, מספק לנו את התוצאה הרצויה, למעט כאשר הערך עבור התקופה הוא 0 מכיוון שתאריכים אלה אינם שייכים לשנת הכספים הראשונה, כמו בחמשת הימים הראשונים של טבלת התאריכים לדוגמה שלנו בלוח השנה. אנחנו יכולים לטפל בזה באמצעות פונקציית IF. במקרה שהתוצאה היא 0, אנו מחזירים 13, כך:
=IF(MOD([Period],13),MOD([Period],13),13)
התוצאה נראית כך:
PivotTable לדוגמה
התמונה שלהלן מציגה PivotTable עם השדה SalesAmount מהטבלה Sales Fact ב- VALUES, ואת השדות PeriodFiscalYear ו- PeriodInFiscalYear מטבלת ממד התאריך של לוח התאריכים בשורות. SalesAmount נצבר עבור ההקשר לפי שנת כספים ותקופה של 28 ימים בשנת הכספים.
קשרי גומלין
לאחר שיצרת טבלת תאריכים במודל הנתונים שלך, כדי להתחיל לעיין בנתונים בטבלאות PivotTable ובדוחות, ולצובר נתונים בהתבסס על העמודות בטבלת ממד התאריך שלך, עליך ליצור קשר גומלין בין טבלת העובדה עם נתוני הטרנזקציה שלך לבין טבלת התאריכים.
מאחר שעליך ליצור קשר גומלין בהתבסס על תאריכים, מומלץ לוודא שאתה יוצר קשר גומלין זה בין עמודות שהערכים שלהן הם מסוג הנתונים תאריך/שעה (תאריך).
עבור כל ערך תאריך בטבלת העובדה, עמודת בדיקת המידע הקשורה בטבלת התאריכים חייבת להכיל ערכים תואמים. לדוגמה, שורה (רשומת עסקה) בטבלת רשימת עובדה של מכירות עם ערך של 15/8/2012 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 מטבלת התאריכים של לוח השנה ממוקמים בשורות.
טבלת PivotTable זו, ישרה קדימה, פועלת כראוי מכיוון שאנחנו רוצים לסכם את סך המכירות לפי תאריך העסקה ב- DateKey. מדיד Total Sales שלנו משתמש בתאריכים ב- DateKey והוא מסוכם לפי שנת כספים ורבעון כספים מאחר שקיים קשר גומלין בין DateKey בטבלה Sales והעמודה Date בטבלת התאריכים של לוח התאריכים.
קשרי גומלין לא פעילים
אך מה אם רצינו לסכם את סך המכירות שלנו לא לפי תאריך העסקה, אלא לפי תאריך המשלוח? אנחנו זקוקים לקשר גומלין בין העמודה ShipDate בטבלה Sales והעמודה Date בטבלה Calendar. אם לא ניצור קשר גומלין זה, הצבירה שלנו תמיד תהיה מבוססת על תאריך העסקה. עם זאת, ייתכן שקיימים קשרי גומלין מרובים, למרות שרק אחד מהם יכול להיות פעיל, ומ מכיוון שתאריך העסקה הוא החשוב ביותר, הוא מקבל את קשר הגומלין הפעיל עם הטבלה לוח שנה.
במקרה זה, ל- ShipDate יש קשר גומלין לא פעיל, ולכן כל נוסחת מידה שנוצרה כדי לצבור נתונים בהתבסס על תאריכי משלוח חייבת לציין את קשר הגומלין הלא פעיל באמצעות הפונקציה USERELATIONSHIP .
לדוגמה, מאחר שקיים קשר גומלין לא פעיל בין העמודה ShipDate בטבלה Sales והעמודה Date בטבלה Calendar, אנו יכולים ליצור מידה המסכם את סך המכירות לפי תאריך המשלוח. אנו משתמשים בנוסחה כגון זו כדי לציין את קשר הגומלין שבו יש להשתמש:
Total Sales by Ship Date:=CALCULATE(SUM(Sales[SalesAmount]), USERELATIONSHIP(Sales[ShipDate], Calendar[Date]))
נוסחה זו פשוט מציינת: חשב סכום עבור SalesAmount, אך סנן באמצעות קשר הגומלין בין העמודה ShipDate בטבלה Sales והעמודה Date בטבלה Calendar.
כעת, אם אנחנו יוצרים PivotTable ומציבים את המידה Total Sales by Ship Date בערכים, וב- Fiscal Year ו- Fiscal Quarter בשורות, אנו רואים את אותו סכום כולל, אך כל הסכומים האחרים עבור שנת כספים ורבעון כספים שונים מאחר שהם מבוססים על תאריך המשלוח ולא על תאריך העסקה.
השימוש בקשרי גומלין לא פעילים מאפשר לך להשתמש בטבלת תאריכים אחת בלבד, אך היא דורשת מדידים כלשהם (כגון Total Sales by Ship Date), להפנות לקשר הגומלין הלא פעיל בנוסחה שלו. קיימת חלופה נוספת, לדוגמה, שימוש בטבלאות תאריכים מרובות.
טבלאות מרובות של תאריכים
דרך נוספת לעבוד עם עמודות תאריך מרובות בטבלת רשימת רשימת הלקוחות היא ליצור טבלאות תאריכים מרובות וליצור קשרי גומלין פעילים נפרדים ביניהן. בוא נבחן שוב את הדוגמה של טבלת המכירות שלנו. יש לנו שלוש עמודות עם תאריכים שבהם ייתכן שתרצה לצבור נתונים:
-
DateKey עם תאריך המכירה עבור כל עסקה.
-
ShipDate – עם התאריך והשעה שבהם הפריטים שנמכרו נשלחו ללקוח.
-
ReturnDate – עם התאריך והשעה שבהם התקבל פריט אחד או יותר.
זכור כי השדה DateKey עם תאריך העסקה הוא החשוב ביותר. אנו נבצע את רוב הצבירה שלנו בהתבסס על תאריכים אלה, ולכן בהחלט נ מעוניין בקשר גומלין ביניהם לבין העמודה 'תאריך' בטבלה 'לוח שנה'. אם איננו מעוניינים ליצור קשרי גומלין לא פעילים בין ShipDate ו- ReturnDate לבין השדה תאריך בטבלה לוח שנה, ובכך לדרוש נוסחאות מידה מיוחדות, נוכל ליצור טבלאות תאריך נוספות עבור תאריך המשלוח ותאריך ההחזרה. לאחר מכן נוכל ליצור קשרי גומלין פעילים ביניהם.
בדוגמה זו, יצרנו טבלת תאריכים אחרת בשם ShipCalendar. משמעות הדבר, כמובן, היא גם יצירת עמודות תאריך נוספות, ומ מכיוון שעמודות תאריך אלה נמצאות בטבלת תאריכים אחרת, אנו רוצים לבחור בהן באופן המבדיל אותן מאותן עמודות בטבלה 'לוח שנה'. לדוגמה, יצרנו עמודות בשם ShipYear, ShipMonth, ShipQuarter וכן הלאה.
אם אנחנו יוצרים את ה- PivotTable ומציבים את המידה של Total Sales בערכים, ואת ShipFiscalYear ו- ShipFiscalQuarter בשורות, אנו רואים את אותן תוצאות שראית כאשר יצרנו קשר גומלין לא פעיל ושדה מחושב מיוחד מסוג Total Sales by Ship Date.
כל אחת מהגישה הללו דורשת שיקולים זהים. בעת שימוש בקשרי גומלין מרובים עם טבלת תאריכים יחידה, ייתכן שיהיה עליך ליצור מדידים מיוחדים ההעברה קשרי גומלין לא פעילים באמצעות הפונקציה USERELATIONSHIP. עם זאת, יצירת טבלאות תאריכים מרובות עשויה להיות מבלבלת ברשימת שדות, ומ מכיוון שיש לך טבלאות נוספות במודל הנתונים, הדבר ידרוש זיכרון רב יותר. התנסה בפריטים המתאימים לך ביותר.
המאפיין 'טבלת תאריך'
המאפיין טבלת תאריך מגדיר מטה-נתונים הדרושים Time-Intelligence כגון TOTALYTD, PREVIOUSMONTH ו- DATESBETWEEN לפעול כראוי. בעת הפעלת חישוב באמצעות אחת מפונקציות אלה, מנגנון הנוסחאות של Power Pivot יודע לאן לעבור כדי לקבל את התאריכים הדרושים לו.
אזהרה: אם מאפיין זה אינו מוגדר, מדידים המשתמשים בפונקציות DAX Time-Intelligence עלולות לא להחזיר את התוצאות הנכונות.
בעת הגדרת המאפיין טבלת תאריכים, עליך לציין טבלת תאריכים ועמודת תאריך של סוג הנתונים תאריך (תאריך ושעה).
כיצד לבצע: הגדרת המאפיין 'טבלת תאריך'
-
בחלון PowerPivot, בחר את הטבלה לוח שנה.
-
בכרטיסיה עיצוב , לחץ על סמן כטבלת תאריכים.
-
בתיבת הדו-שיח סימון כטבלת תאריכים, בחר עמודה עם ערכים ייחודיים וסוג הנתונים 'תאריך'.
עבודה עם זמן
כל ערכי התאריכים עם סוג הנתונים 'תאריך' ב- Excel SQL Server הם למעשה מספר. המספר הכלול במספר זה הוא ספרות המפנות לזמן. במקרים רבים, הזמן עבור כל שורה הוא חצות. לדוגמה, אם שדה DateTimeKey בטבלת עובדה של מכירות כולל ערכים כגון 19/10/2010 12:00:00, משמעות הדבר היא שהערכים הם ברמת הדיוק של היום. אם ערכי השדה DateTimeKey כוללים שעה, לדוגמה, 19/10/2010 08:44:00, משמעות הדבר היא שהערכים הם ברמת הדיוק הדקות. הערכים יכולים להיות גם ברמת הדיוק של השעה, או אפילו ברמת הדיוק של שניות. רמת הדיוק בערך הזמן תשפיע באופן משמעותי על האופן שבו תיצור את טבלת התאריכים שלך ועל קשרי הגומלין ביניהם לבין טבלת עובדה.
עליך לקבוע אם לצבור את הנתונים שלך לרמת דיוק של יום או לרמת דיוק של זמן. במילים אחרות, ייתכן שתרצה להשתמש בעמודות בטבלת התאריכים, כגון שדות בוקר, אחר הצהריים או שעה כשדות תאריך שעה באזורי שורה, עמודה או סינון של PivotTable.
הערה: ימים הם יחידת הזמן הקטנה ביותר שפונקציות DAX Time Intelligence יכולות לעבוד איתם. אם אינך צריך לעבוד עם ערכי זמן, עליך לצמצם את מידת הדיוק של הנתונים כדי להשתמש בימים כיחידת המינימום.
אם בכוונתך לצבור את הנתונים שלך לפי רמת השעה, טבלת התאריכים תזדקק לעמודת תאריך עם השעה הכלולה. למעשה, היא תצטרך עמודת תאריך עם שורה אחת עבור כל שעה, או אפילו כל דקה, של כל יום, עבור כל שנה בטווח התאריכים. זאת משום ש כדי ליצור קשר גומלין בין העמודה DateTimeKey בטבלת העובדה לבין עמודת התאריך בטבלת התאריכים, דרושים לך ערכים תואמים. כפי שאתה יכול לדמיין, אם תכלול שנים רבות, זה יכול ליצור טבלת תאריכים גדולה מאוד.
עם זאת, ברוב המקרים, ברצונך לצבור את הנתונים ליום בלבד. במילים אחרות, תשתמש בעמודות כגון שנה, חודש, שבוע או יום בשבוע כשדות באזורי שורה, עמודה או מסנן של PivotTable. במקרה זה, עמודת התאריך בטבלת התאריכים צריכה להכיל שורה אחת בלבד עבור כל יום בשנה, כפי שתיארנו קודם לכן.
אם עמודת התאריך שלך כוללת רמת דיוק של זמן, אך תצטבור ברמת יום בלבד, כדי ליצור את קשר הגומלין בין טבלת העובדה לבין טבלת התאריכים, ייתכן שתצטרך לשנות את טבלת רשימת עובדה על-ידי יצירת עמודה חדשה שמחוחזרת את הערכים בעמודה 'תאריך' לערך יום. במילים אחרות, המר ערך כגון 19/10/2010 08:44:00ל- 10/19/2010 00:00:00. לאחר מכן תוכל ליצור את קשר הגומלין בין עמודה חדשה זו לעמודת התאריך בטבלת התאריכים מאחר שהערכים תואמים.
בוא נבחן דוגמה. תמונה זו מציגה עמודת DateTimeKey בטבלת רשימת עובדה של מכירות. כל הצבירה של הנתונים בטבלה זו צריכה להיות ברמת היום בלבד, באמצעות עמודות בטבלת התאריכים של לוח השנה, כגון שנה, חודש, רבעון וכן הלאה. השעה הכלולה בערך אינה רלוונטית, רק התאריך בפועל.
מאחר שאין צורך לנתח נתונים אלה לפי רמת הזמן, אין צורך שהעמודה 'תאריך' בטבלת התאריכים 'לוח שנה' תכלול שורה אחת עבור כל שעה וכל דקה של כל יום בכל שנה. לכן, העמודה תאריך בטבלת התאריכים שלנו נראית כך:
כדי ליצור קשר גומלין בין העמודה DateTimeKey בטבלה Sales והעמודה Date בטבלה Calendar, ניתן ליצור עמודה מחושבת חדשה בטבלה Sales Fact, ולהשתמש בפונקציה TRUNC כדי לחתוך את ערך התאריך והשעה בעמודה DateTimeKey לערך תאריך התואם לערכים בעמודה 'תאריך' בטבלה 'לוח שנה'. הנוסחה שלנו נראית כך:
=TRUNC([DateTimeKey],0)
פעולה זו מעניקה לנו עמודה חדשה (ששמה DateKey) עם התאריך מהעמודה DateTimeKey ושעה של 12:00:00 עבור כל שורה:
כעת ניתן ליצור קשר גומלין בין העמודה החדשה (DateKey) לעמודה 'תאריך' בטבלה 'לוח שנה'.
באופן דומה, ניתן ליצור עמודה מחושבת בטבלה Sales שמפחיתה את דיוק הזמן בעמודה DateTimeKey לרמת הדיוק של השעה. במקרה זה, הפונקציה TRUNC לא תפעל, אך עדיין נוכל להשתמש בפונקציות אחרות של תאריך ושעה של DAX כדי לחלץ ערך חדש ולשרשר אותו מחדש לרמת דיוק של שעה. אנו יכולים להשתמש בנוסחה כגון זו:
= DATE (YEAR([DateTimeKey]), MONTH([DateTimeKey]), DAY([DateTimeKey]) ) + TIME (HOUR([DateTimeKey]), 0, 0)
העמודה החדשה שלנו נראית כך:
בתנאי שעמודת התאריך בטבלת התאריכים מכילה ערכים לפי רמת הדיוק של השעה, נוכל ליצור קשר גומלין ביניהם.
הפיכת תאריכים לשימושיים יותר
רבות מעמודות התאריכים שאתה יוצר בטבלת התאריכים שלך נחוצה עבור שדות אחרים, אך לא כל אלה שימושיים בניתוח. לדוגמה, השדה DateKey בטבלה Sales שאליו הפנו והצגנו לאורך מאמר זה חשוב מאחר שעבור כל עסקה, עסקה זו מתועדת כטרנזקציה המתרחשת בתאריך ובשעה מסוימים. אך מתוך נקודת מבט של ניתוח ודיווח, לא כל זה שימושי מאחר שלא ניתן להשתמש בו כשדה שורה, עמודה או סינון ב- Pivot Table או כדוח.
באופן דומה, בדוגמה שלנו, העמודה תאריך בטבלה לוח שנה שימושית מאוד, קריטית למעשה, אך לא ניתן להשתמש בה כממד ב- PivotTable.
כדי להשאיר טבלאות והעמודות בהן שימושיות ככל האפשר, הפוך את רשימות השדות של דוחות PivotTable או Power View לקלות יותר לניווט, חשוב להסתיר עמודות מיותרות מכלי הלקוח. ייתכן שתרצה גם להסתיר טבלאות מסוימות. הטבלה Holidays שהוצגה קודם מכילה תאריכי חגים החשובים עבור עמודות מסוימות בטבלה לוח שנה, אך לא ניתן להשתמש בעמודות תאריך וחג בטבלה Holidays עצמן כשדות ב- PivotTable. כאן שוב, כדי להפוך את רשימות לקל יותר לניווט, באפשרותך להסתיר את טבלת החגים כולה.
היבט חשוב נוסף של עבודה עם תאריכים הוא מוסכמות למתן שמות. באפשרותך לבחור טבלאות ועמודות בכל שם שתרצה ב- Power Pivot. אך זכור, במיוחד אם אתה משתף את חוברת העבודה עם משתמשים אחרים, מוסכמה טובה למתן שמות מקלה עליך לזהות טבלאות ותאריכים, לא רק בשדה שדה רשימות, אלא גם ב- Power Pivot ובנוסחאות DAX.
לאחר שיש לך טבלת תאריכים במודל הנתונים שלך, תוכל להתחיל ליצור מדידים שיעזרו לך להפיק את המרב מהנתונים שלך. חלקן עשויות להיות פשוטות כמו סיכום סכומי המכירות עבור השנה הנוכחית, ואחרות עשויות להיות מורכבות יותר, שבהן עליך לסנן לפי טווח מסוים של תאריכים ייחודיים. קבל מידע נוסף במודדים בפונקציות Power Pivotובינת זמן.
נספח
המרת תאריכים של סוג נתונים של טקסט לסוג נתונים של תאריך
במקרים מסוימים, טבלת עובדה עם נתוני טרנזקציות עשויה להכיל תאריכים של סוג נתונים של טקסט. לדוגמה, תאריך שמופיע כתאריך 2012-12-04T11:47:09 אינו תאריך כלל, או לפחות לא את סוג התאריך שבו Power Pivot יכול להבין. זה באמת רק טקסט שנקרא כמו תאריך. כדי ליצור קשר גומלין בין עמודת תאריך בטבלת העובדה, לבין עמודת תאריך בטבלת תאריכים, שתי העמודות חייבות להיות מסוג הנתונים 'תאריך '.
בדרך כלל, כאשר אתה מנסה לשנות את סוג הנתונים עבור עמודה של תאריכים מסוג נתוני טקסט לסוג נתונים של תאריך, Power Pivot יכול לפרש את התאריכים ולהמיר אותו לסוג נתונים אמיתי של תאריך באופן אוטומטי. אם ל- Power Pivot אין אפשרות לבצע המרת סוג נתונים, תקבל שגיאת אי-התאמה של סוגים.
עם זאת, עדיין תוכל להמיר את התאריכים לסוג נתונים של תאריך אמיתי. באפשרותך ליצור עמודה מחושבת חדשה ולהשתמש בנוסחת DAX כדי לנתח את מבנה הטקסט של השנה, החודש, היום, השעה וכן הלאה, ולאחר מכן לשרשר אותה בחזרה יחד באופן שבו Power Pivot יכול לקרוא כתאריך אמיתי.
בדוגמה זו, ייבנו טבלת עובדה בשם Sales לתוך Power Pivot. היא מכילה עמודה בשם DateTime. ערכים מופיעים כך:
אם נבחן את סוג הנתונים בקבוצה 'עיצוב' בכרטיסיה 'בית' של Power Pivot, נראה שזהו סוג הנתונים 'טקסט'.
לא ניתן ליצור קשר גומלין בין העמודה DateTime והעמודה 'תאריך' בטבלת התאריכים שלנו מאחר שהסוגים אינם תואמים. אם ננסה לשנות את סוג הנתונים ל'תאריך', אנו מקבלים שגיאת אי-התאמה של סוג:
במקרה זה, ל- Power Pivot לא היתה אפשרות להמיר את סוג הנתונים מטקסט לתאריך. אנחנו עדיין יכולים להשתמש בעמודה זו, אך כדי להפוך אותה לסוג נתונים אמיתי של תאריך, עלינו ליצור עמודה חדשה המנתחת את הטקסט ומיצור אותה מחדש לערך ש- Power Pivot יכול להפוך לסוג נתונים של תאריך.
זכור, מתוך סעיף עבודה עם זמן מוקדם יותר במאמר זה; אלא אם כן יש צורך בניתוח ברמת דיוק של שעה ביום, עליך להמיר תאריכים בטבלת רשימת העובדה לרמה מדויקת של יום. לכן, אנו רוצים שהערכים בעמודה החדשה שלנו יהיו ברמת הדיוק של היום (לא כולל זמן). ניתן גם להמיר את הערכים בעמודה DateTime לסוג נתונים של תאריך ולהסיר את רמת הדיוק של השעה באמצעות הנוסחה הבאה:
=DATE(LEFT([DateTime],4), MID([DateTime],6,2), MID([DateTime],9,2))
פעולה זו מספקת עמודה חדשה (במקרה זה, בשם Date). 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))
כעת, לאחר שיש לנו עמודה מסוג הנתונים 'תאריך', נוכל ליצור קשר גומלין ביניהם לבין עמודת תאריך בתאריך.
משאבים נוספים
התחלה מהירה: למד את העקרונות הבסיסיים של DAX ב- 30 דקות
