در این آموزش ایجاد یک جدول تاریخ را خواهیم آموخت. یکی از نیازهایی که کسب و کارهای مختلف در حین ایجاد گزارش در پاور بی آی دارند، انجام یک سری محاسبات برمبنای تاریخ و زمان می باشد. سازمان ها می خواهند مطلع باشند که وضعیت تجاری آنها در طول ماه ها، فصل ها، سال های مالی و غیره چه شرایطی خواهد داشت. به همین دلیل فرمت گذاری صحیح این مقادیر وابسته به زمان اهمیت زیادی دارد. پاور بی آی به صورت خودکار ستون ها و جداول تاریخ را تشخیص می دهد؛ با این وجود ممکن است شرایطی پیش بیاید که برای بدست آوردن تاریخ های دارای فرمت مورد نیاز سازمانتان به یک سری مراحل و اقدامات بیشتری نیاز پیدا شود.

برای دیدن تمام آموزش های پاور بی آی به لینک زیر مراجعه کنید:

دوره رایگان و فیلم آموزش پاور بی آی (power BI)

مثال

برای مثال فرض کنید که شما در سازمان خودتان مسئول تهیه گزارش برای تیم Sales باشید. دیتابیس موجود شامل جداولی برای فروش ها، سفارشات، محصولات و سایر موارد می باشد. همانطور که در ستون های ShipDate و OrderDate جداول Sales و Orders نشان داده شده است، اکثر این جداول (از جمله Sales و Orders) یک سری ستون های تاریخ برای خودشان دارند. از شما خواسته شده است که یک جدول فروش و سفارشات کل به صورت سالیانه و ماهانه تهیه کنید. به چه نحوی می توانید جداول مختلفی را تهیه کنید که هر یک از آنها به ستون های تاریخ خودش اشاره کند؟

به منظور حل این مسئله می توانید یک جدول تاریخ مشترکی را تهیه کنید که در جداول مختلف قابل استفاده باشد. در ادامه نحوه انجام این کار در پاور بی آی توضیح داده شده است.

ایجاد یک جدول تاریخ مشترک

روش هایی که با استفاده از آن ها می توانید یک جدول تاریخ مشترک درست کنید عبارتند از:

• داده های منبع
• DAX
• Power Query

داده های منبع

در برخی مواقع پایگاه های داده ای منابع و انباره داده ها از قبل یک سری جداول تاریخ مختص خودشان دارند. در صورتیکه مدیر طراحی کننده دیتابیس کار خودش را بصورت کامل انجام داده باشد، از جداول موجود می توان برای انجام دادن موارد زیر استفاده کرد:

• مشخص کردن روزهای تعطیل شرکت
• جدا کردن تقویم و سال مالی
• مشخص کردن تعطیلات آخر هفته در مقایسه با روزهای هفته

جداول داده های منابع کامل و آماده استفاده آنی هستند. در صورتیکه چنین جدولی داشته باشید، آن را وارد مدل داده ای خودتان کنید و از روش های دیگری که در ادامه توضیح داده می شوند استفاده نکنید. توصیه ما استفاده از یک جدول تاریخ منبع می باشد چرا که احتمالا در ابزارهایی غیر از پاور بی آی که ممکن است استفاده کنید، این جدول تاریخ مشترک خواهد بود.

در صورتیکه جدول داده ای منبع نداشته باشید، با استفاده از یک سری روش ها می توانید یک جدول تاریخ مشترک را ایجاد کنید.

DAX

بمنظور ساختن جدول تاریخ مشترک می توانید از توابع Data Analysis Expression (DAX) (به معنای بیان تجزیه و تحلیل داده ها) CALENDARAUTO() یا ()CALENDAR استفاده کنید. تابع ()CALENDAR برمبنای تاریخ شروع و پایان که به عنوان آرگومان در تابع وارد شده است، محدوده متناوبی از تاریخ ها را بصورت دوره ای برمی گرداند. همچنین تابع ()CALENDAEAUTO یک محدوده پیوسته و کاملی از تاریخ ها را برمی گرداند که به صورت خودکار از مجموعه داده های شما تعیین می شود.

تاریخ شروع به عنوان اولین تاریخ موجود در مجموعه داده های شما تعیین می شود و تاریخ پایان آخرین تاریخ موجود در مجموعه داده  شما و همچنین داده هایی قرار دارد که برای ماه مالی جمع شده است (این ماه مالی عبارت از ماهی می باشد که شما انتخاب کرده اید تا به عنوان آرگومان در تابع ()CALENDARAUTO شامل شود. در این مثال تابع ()CALENDAR استفاده شده است به دلیل اینکه هدف شما عبارت از مشاهده کردن داده های موجود از 31 می 2011 (اولین روزی که Sales در داده ها قرار دارد) تا 10 سال آینده می باشد.

در Power BI Desktop در ریبون به تب Table بروید. New Table را انتخاب کنید و بعد از آن فرمول DAX زیر را وارد کنید.

در این مرحله یک ستون از تاریخ ها دارید که می توانید استفاده کنید. با این وجود این ستون تا یک حدی پراکنده می باشد. هدف از این کار عبارت از مشخص کردن سال، شماره ماه، هفته مربوط به سال و روز مربوط به هفته می باشد. این کار را می توانید از طریق انتخاب کردن New Column در ریبون و وارد کردن معادله DAX بعدی انجام دهید که سال را از جدول Date بازیابی می کند.

بمنظور بازیابی شماره ماه، شماره هفته و روز مربوط به هفته هم از همین پروسه استفاده کنید:

بعد از اتمام این کار، جدول شما ستون های نشان داده شده در شکل زیر را خواهد داشت.

تا این مرحله با استفاده از DAX یک جدول تاریخ مشترک را ایجاد کردید. این فرآیند فقط جدول جدیدتان را وارد مدل داده ها می کند؛ همچنان نیاز دارید که یک سری روابطی را مابین جداول تاریخ و جداول Sales و Order ایجاد کنید و بعد از آن یک جدولی را به عنوان جدول تاریخ رسمی مدل داده ای خودتان مشخص کنید. با این وجود قبل از انجام این کار، یک روش دیگری (با استفاوه از Power Query) را برای ایجاد یک جدول تاریخ مشترک یاد بگیرید.

 Power Query

بمنظور تعریف یک جدول تاریخ مشترک می توانید از زبان M به عنوان یک زبان توسعه ای استفاده کنید (که قبلا برای ساختن کوئری ها در Power Query استفاده کردید).

در Power BI Desktop، گزینه Transform Data را انتخاب کنید تا وارد Power Query شوید. در فضای خالی مربوط به صفحه Queries، راست کلیک کنید تا منوی کشویی زیر نمایش داده شود و در آن New Query و بعد از آن Blank Query را انتخاب کنید.

در نمای New Query بدست آمده، فرمول M زیر را وارد کنید تا یک جدول تاریخ بسازید:

برای داده های فروش خودتان تاریخ شروع شما باید اولین تاریخی را نشان دهد که در داده های خودتان دارید (که عبارت از 31 می 2011 می باشد). علاوه بر این، تاریخ های مربوط به 10 سال آینده را می خواهید ببینید که شامل تاریخ های آینده می باشد. این روش باعث می شود که همراه با ورود اطلاعات جدید، هیچ نیازی به ایجاد مجدد این جدول نداشته باشید. همچنین شما می توانید مدت زمان را تغییر دهید. در این حالت شما یک نقطه داده برای هر روز می خواهید، ولی همچنین می توانید ساعات، دقایق و ثانیه ها را افزایش دهید. شکل زیر نتایج بدست آمده را نشان می دهد.

بعد از انجام موفق این پروسه به جای یک جدول از تاریخ ها یک لیست از تاریخ ها خواهید داشت. به منظور تصحیح این خطا، در ریبون به تب Transform بروید و بعد از آن Covert و To Table را انتخاب کنید. همانطور که از عنوان موجود هم مشخص است، این ویژگی لیست شما را به یک جدول تبدیل خواهد کرد. همچنین می توانید اسم ستون را به DateCol تغییر دهید.

هدف بعدی شما عبارت از اضافه کردن ستون ها به جدول جدید می باشد تا تاریخ های موجود را بر اساس سال، ماه، هفته و روز مشاهده کنید تا بتوانید در تصویر خودتان یک سلسله مراتب بسازید. اولین کاری که باید انجام دهید عبارت از تغییر نوع ستون از طریق انتخاب آیکون موجود در کنار اسم ستون و انتخاب کردن نوع Date در منوی کشویی ظاهر شده می باشد.

بعد از انتخاب نوع Date می توانید برای سال، ماه ها، هفته ها و روزها چند ستون اضافه کنید. مطابق شکل زیر به Add Column بروید، منوی کشویی موجود در زیر Date را انتخاب کنید و بعد از آن Year را انتخاب نمایید.

برای تمامی سال هایی که از DateCol وارد شده اند، پاور بی آی یک ستون اضافه می کند.

همین پروسه را برای ماه ها، هفته ها و روزها انجام دهید. همانطور که در تصویر زیر هم نشان داده شده است، بعد از انجام دادن این کار جدول شامل این ستون ها خواهد بود.

تا این مرحله توانستید با استفاده از Power Query یک جدول تاریخ مشترک بسازید.

مراحل قبلی نحوه بدست آوردن یک جدول در مدل داده ای را نشان می دهند. در این مرحله باید جدول خودتان را به عنوان جدول تاریخ رسمی نشانه گذاری کنید تا پاور بی آی برای تمامی مقادیر آینده بتواند آن را شناسایی کند و همچنین نسبت به صحیح بودن فرمت گذای مطمئن شود.

نشانه گذاری به عنوان جدول تاریخ رسمی

اولین کاری که باید در نشانه گذاری به عنوان جدول تاریخ رسمی انجام دهید عبارت از پیدا کردن یک جدول جدید در صفحه Finds می باشد. همانطور که در شکل زیر نشان داده شده است، بر روی اسم جدول کلیک کنید و Mark as date table را انتخاب کنید.

بعد از نشانه گذاری جدول تان به عنوان جدول تاریخ، پاور بی آی یک سری اعتبارسنجی هایی انجام می دهد تا مطمئن شود که داده ها در طول یک دوره دارای مقادیر خالی صفر، منحصر بفرد و مداوم هستند. همچنین شما می توانید ستون های خاصی را در جدول خودتان به عنوان جدول نشانه گذاری کنید تا در حین وجود داشتن ستون های زیاد در داخل جدول بتوانید مورد استفاده قرار دهید. بر روی جدول کلیک راست کنید، Mark as date table را انتخاب کنید و بعد از آن Date table settings را انتخاب کنید. پنجره زیر نمایش داده خواهد شد که در آن می توانید ستون هایی که باید به عنوان Date نشانه گذاری شوند را انتخاب کنید.

در جدولی که به عنوان جدول تاریخ نشانه گذاری کردید، انتخاب Mark as date table منجر به حذف سلسله مراتبی خواهد شد که به صورت خودکار در فیلد Date تشکیل شده اند. سلسه مراتب خودکار برای سایر فیلدهای تاریخ تا زمانی  وجود خواهند داشت که شما یک رابطه ای مابین این فیلد و جدول تاریخ ایجاد کنید یا تا زمانی که شما ویژگی Auto Date/Time را خاموش کنید. با کلیک راست کردن بر روی ستونهای سال، ماه، هفته یا روز در صفحه Fields و انتخاب New hierarchy، به صورت دستی می توانید یک سلسله مراتبی را به تاریخ مشترک خودتان اضافه کنید. این پروسه در بخش های بعدی بیشتر توضیح داده خواهد شد.

تصاویر خودتان را بسازید

به منظور ایجاد تصویر مابین جداول Sales و Orders، نیاز خواهید داشت که یک رابطه ای را مابین جدول تاریخ مشترک جدید و جداول Sales  و Orders ایجاد کنید. در نتیجه با استفاده از جدول تاریخ جدید قادر خواهید بود تصاویر را بسازید. به منظور انجام دادن اینکار به جدول Model و Manage Relationships (،جائیکه با استفاده از ستون OrderDate می توانید مابین جدول تاریخ مشترک و جداول Orders و Sales  روابطی ایجاد کنید) بروید. تصویر زیر یک نمونه از روابط بیان شده می باشد.

بعد از ایجاد روابط می توانید تصویر Total Sales and Order Quantity by Time را با جدول تاریخ مشترکی بسازید که با استفاده از روش DAX یا Power Query ایجاد کرده اید.

به منظور مشخص کردن فروش کل باید تمامی فروش ها را اضافه کنید چرا که ستون Amount موجود در جدول Sales فقط درآمد مربوط به هر فروش را مشاهده می کند نه جمع کل درآمد فروش. با استفاده از محاسبه اندازه گیری زیر می توانید این کار را انجام دهید (این موضوع در بحثهای بعدی توضیح داده خواهند شد). محاسبه ای که در حین اندازه گیری مورد استفاده قرار خواهید داد به صورت زیر می باشد:

بعد از اتمام اینکار، از طریق برگشتن به تب Visualizations و انتخاب تصویر Table می توانید یک جدولی را ایجاد کنید. هدف شما مشاهده کل سفارشات و فروش ها در ماه و سال می باشد؛ بنابراین در جدول تاریخ خودتان باید ستون های Year و Month، ستون OrderQty و ابزار اندازه گیری #TotalSales را انتخاب کنید. بعد از یادگیری مطالب لازم در مورد سلسله مراتب، می توانید یک سلسله مراتبی بسازید که امکان رفتن از سال ها به ماه ها را فراهم کند. برای مثال می توانید آنها را در کنار هم مشاهده کنید. تا این مرحله با استفاده از جدول تاریخ مشترک توانستید یک تصویری را ایجاد کنید.

در قسمت بعدی کار کردن با ابعاد را خواهیم آموخت.