جدول ابعاد تاریخ که به آن DimDate گفته می‌شود، یک تقویم جامع است که به هر تاریخ یک ردیف اختصاص می‌دهد و امکان گروه‌بندی و تحلیل داده‌ها بر اساس بازه‌های زمانی مانند سال، ماه و روز را فراهم می‌کند. جدول dimDate نوشتن محاسبات DAX را ساده کرده و امکان ایجاد گزارش‌های یکپارچه و دقیق بر مبنای زمان را فراهم می‌کند.
نحوه محاسبه DimDate تاریخ شمسی فرساران
در این روش شما می‌توانید به صورت پویا (داینامیک) تمامی تقویم تاریخ شمسی یا همان dimDate شمسی را توسط یک کوئری در Power Query تولید کنید.
در ویدئوی زیر آموزش این روش را خواهید یافت و همچنین در قسمت فایل‌های پیوست همین مقاله، می‌توانید فایل تقویم تاریخ شمسی در Power BI و یا تقویم شمسی در Excel مورد نیاز پروژه خود را دانلود کنید.
تابع Date.ToText در Power Query
مبنای اصلی ساخت dimDate (تقویم شمسی) تابع Date.ToText است. با این توابع می‌توان یک تاریخ میلادی را به تاریخ تقویم هر کشوری تبدیل کرد و شکل آن برای تبدیل تاریخ شمسی اینگونه است:
=Date.ToText( [order date] , [Format = "yyy/MM/dd", Culture = "fa-IR"] )توجه: حروف کوچک و بزرگ در این فرمول دقت کنید.
همچنین به صورت خلاصه‌تر هم می‌توان فرمول را به شکل زیر نوشت:
=Date.ToText( [order date] , [Format = "yyy/MM/dd", Culture = "fa"] )و یا برای محاسبه ماه قمری (مورد نیاز شرکت‌های بیمه در ایران) فرمول زیر بکار می‌رود:
=Date.ToText( [order date] , [Format = "yyy/MM/dd", Culture = "ar-SA"] )محاسبه اختلاف بین دو تاریخ شمسی در Power BI
از آنجایی که در زبان DAX بر روی تاریخ شمسی هیچ محاسبه‌ای نمی‌توان انجام داد، باید تمامی تاریخ‌های شمسی به معادل تاریخ میلادی آنها تبدیل شوند و سپس بر روی این تاریخ میلادی محاسباتی مانند اختلاف با یک تاریخ دیگر را محاسبه کرد. در ویدئوی زیر تبدیل تاریخ شمسی به میلادی در Power BI آموزش شرح داده شده است:
تابع Date.FromText در پاورکوئری
این تابع تاریخ شمسی را به صورت یک متن به عنوان ورودی دریافت می‌کند و سپس آن را به یک تاریخ معتبر میلادی تبدیل می‌کند:
Date.FromText( "1357/05/17" , [Format = "yyyy/MM/dd", Culture = "fa"])
محاسبه داینامیک تاریخ شروع جدول تاریخ شمسی
برای آنکه تاریخ شروع جدول ما به صورت داینامیک محاسبه شروع شود، می‌توانیم سال اولین تاریخ میلادی موجود در یکی از جداول اصلی مثلا factSales را با روش زیر محاسبه کنیم و در Step کوئری به نام StartDate از این فرمول استفاده کنیم:
= #date( Date.Year( List.Min(factSales[Order Date]) ), 1, 1)محاسبه داینامیک تاریخ شروع جدول تاریخ شمسی
ایده اصلی بکار گرفته شده در این جدول تقویم آن است که تاریخ پایان جدول، یکسال بعد از تاریخ جاری سیستم باشد و برای اینکار از فرمول زیر استفاده می‌کنیم و می‌توانیم عدد + 1 را در فرمول زیر به مقدار دلخواه تغییر دهیم:
= #date( Date.Year(DateTime.LocalNow()) + 1, 1, 1)محاسبه شماره هفته در تقویم ایران
ظاهرا تقویم هجری شمسی دارای مشکلاتی است که در مقالات تخصصی سایت «مرکز تقویم دانشگاه تهران» اشاره شده است که هنوز مرتفع نشده‌اند و البته در این سایت هیچ معیاری برای محاسبه شماره هفته در تقویم سال مشخص نشده است و به همین دلیل اینجانب در محاسبه شماره هفته سال از فرمول زیر (که در منابع انگلیسی به آن اشاره شده است) استفاده کرده‌ام:
Number.RoundDown( ([P.Day of Year] - [P.Day of Week] + 10) / 7 , 0)در این فرمول P.Day of Year اشاره به چندمین روز سال دارد و P.Day of Week اشاره به شماره روز در هفته می‌کند.
شایان ذکر است که در سایر نسخه‌های dimDate که در سایت‌های دیگر منتشر شده است هیچ دقتی بر روی این موضوع نشده و شماره هفته اشتباه است.
نمایش روز جاری در Power BI
برای نمایش روز جاری تقویم شمسی کافی است که تاریخ میلادی را در dimDate با تابع LOOKUPVALUE و مقدار متناظر تاریخ شمسی آن را بیابیم و این Measure را می‌نویسیم:
Persian Today =
LOOKUPVALUE(dimDate[P.Date], dimDate[Date], TODAY())نمایش آخرین تاریخ به روز رسانی / Refresh در Power BI
برای اینکار یک کوئری ساده در Power Query می‌سازیم که تاریخ روی جاری را به میلادی برگرداند:
let
Source = #table(type table[Last Refreshed=datetime],
{{DateTime.LocalNow()}})
in
Sourceسپس این تاریخ را در جدول dimDate با تابع LOOKUPVALUE جستجو و مقدار متناظر شمسی آن را می‌یابیم و این Measure را می‌نویسیم:
Persian Last Refreshed =
VAR dt = MIN('Today'[Last Refreshed])
RETURN
LOOKUPVALUE(dimDate[P.Date], dimDate[Date], int(dt))
& FORMAT(dt, " hh:mm:ss")کارهای بعد از Load کردن dimDate در Power BI
بعد از آنکه تاریخ شروع و پایان کوئری dimDate را مشخص کردید و دادهای این جدول را در Data Model بارگذاری شد، کارهای زیر را انجام دهید:
۱) Data Type ستون‌ها را بررسی کنید و اگر نیاز است آنها را تصحیح نمایید. توجه داشته باشید که در کوئری اصلی Data Type ها تنظیم شده‌اند و احتمالا نیازی به تغییر نخواهد بود.
۲) باید تمامی ستون‌های عددی را انتخاب و summarization آنها را یک به یک در حالت Don't summarize قرار دهید:
۳) باید مرتب سازی ستون‌هایی که به صورت متنی هستند مانند «نام ماه» با گزینه Sort by column بر اساس P.Month (عدد ماه) انجام شود. اینکار باید برای «نام روز هفته»، «نام فصل» و اعداد متناظر آنها جداگانه انجام شود.
۴) برای استفاده از قابلیت Drill Down/Up، می‌توانید یک Hierarchy درست کنید. برای اینکار ابتدا روی P.Year (بالاترین مرتبه سلسله مراتب) در فهرست فیلدها،R-click کنید و گزینه Create hierarchy را بزنید. نام این سلسله مراتب را به دلخواه تغییر دهید.
سپس به ترتیب بر روی فیلدهای P. Month Name و P. Day of Month که می‌خواهید به این سلسله مراتب اضافه شوند، R-Click کنید و گزینه Add to hierarchy را انتخاب کنید و بر روی نام سلسله مراتب که ساخته‌اید، کلیک کنید.
توجه : این آموزش ادامه دارد ....
لازم به ذکر است که بخشی از این آموزش برای اولین بار در ایران از وب سایت فرساران منتشر شده است و لذا خواهشمندست که در بازنشر این روش/آموزش به منبع آن یعنی وب سایت فرساران ارجاع داده شود.
منابع:
مشاهده سایر Culture nameها
به عنوان اولین نفر، تجربه یا دیدگاه خود را بنویسید!