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