بهترین راه ایجاد جدول داینامیک تقویم شمسی و میلادی در نرم افزار Power BI

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

روش های ایجاد جدول تقویم شمسی در Power BI

برای رفع مشکل تقویم شمسی در Power BI ، لازم است یک جدول (DimDate) به‌عنوان جدول تاریخ شمسی اضافه شود که در آن، ستون‌های مختلف مرتبط با بازه زمانی وجود دارند. در جدول مذکور، یک ستون تاریخ میلادی، برای برقراری Relationship بین جدول DimDate و سایر جداول موردنیازتان وجود دارد. این جدول کاربردی، ابزاری مناسبی برای تجزیه و تحلیل داده‌ها در بازه‌های زمانی در زمان مدل‌سازی داده‌ها به شمار می‌رود.

پیش بررسی بیش‌تر مبحث استفاده از تاریخ شمسی در Power BI ، توجه کنید که روش‌های زیر، از لحاظ عملکردی (Performance)، تفاوت چندانی باهم نخواهند داشت. تفاوت اصلی آن‌ها در سرعت، سادگی و داینامیک بودن‌شان در زمان استفاده است. درحال حاضر، جهت ایجاد جدول تقویم شمسی در Power BI یا همان جدول DimDate چندین راهکار زیر وجود دارند.

روش های مرسوم استفاده از تقویم شمسی در Power BI

برای استفاده از تقویم شمسی در Power BI ، چند رویکرد رایج زیر پیش روی شما وجود دارند:

1. Import کردن جدول تقویم شمسی آماده (در قالب فرمت‌های اکسل یا CSV در نرم افزار Power BI)
2.  ایجاد جدول تقویم شمسی ازطریق ایجاد کوئری در SQL Server و انواع پایگاه داده
3. ایجاد جدول تقویم شمسی ازطریق کد زبان M در پاور کوئری

معایب روش های متداول

روش‌های فوق، معایب و کاستی‌های زیر را دارا هستند:

1. تغییرات اعمال شده در جدول به‌صورت داینامیک و پویا انجام نمی‌شوند.
2. تغییرات در تعریف بازه زمانی و همچنین، افزایش و کاهش تعداد ستون‌ها در جدول تقویم شمسی، انعطاف‌پذیری ندارند.

با توجه به کاستی‌های مذکور، بهتر است از بهینه‌ترین راهکار ایجاد جدول داینامیک تقویم شمسی و میلادی در نرم افزار Power BI استفاده شود. در ادامه، به بررسی بهترین روش استفاده از تقویم شمسی در Power BI می‌پردازیم.

بهترین روش: ایجاد جدول تقویم شمسی (DimDate) 

بهترین روش برای کار و استفاده از تقویم شمسی در Power BI ، ایجاد جدول تقیوم شمسی داینامیک با استفاده از زبان M در پاور کوئری است. شما می‌توانید با انجام مراحل زیر و استفاده از کد نمایش داده شده، به‌راحتی جدول تاریخ شمسی را در Power Query ایجاد کنید. بدین طریق، امکان انتخاب بازه زمانی موردنظر و ستون‌های مدنظر برای شما ممکن خواهد بود.

۱- ورود به بخش Power Query

در وهله اول، لازم است نرم افزار Power BI را اجرا کرده و به بخش Power Query مراجعه کنید. سپس از منو بالا، گزینه New Source را انتخاب کرده و روی گزینه Blank Query کلیک کنید.

۲- افزودن کوئری مربوط به ایجاد جدول شمسی

پس از آن که به صفحه Power Query وارد شدید، روی گزینه Advanced Editor کلیک کرده تا صفحه پاپ آپ نمایش داده شود. اکنون کد کوئری نوشته شده زیر را کپی کنید و آن را در کادر Advanced Editor قرار دهید. این کوئری با استفاده از زبان M نوشته شده است. درنهایت، روی گزینه Done کلیک نمائید تا این کوئری اجرا شود.

let DateShamsiandmiladi = (
  Start.Date as date,
  optional End.Date as date,
  optional IncludeJalali2 as logical,
  optional IncludeJalali3 as logical,
  optional IncludeJDateKey as logical,
  optional IncludeJDateKey1 as logical,
  optional IncludeJYear as logical,
  optional IncludeJMonthN as logical,
  optional IncludeJDayN as logical,
  optional IncludeJDayN1 as logical,
  optional IncludeJMonthT as logical,
  optional IncludeJHalfYear as logical,
  optional IncludeJSeasonT as logical,
  optional IncludeJDayOfYear as logical,
  optional IncludeJDayT as logical,
        	optional IncludeYYYYMM as logical,
        	optional IncludeQuarterN as logical,
        	optional IncludeMyear as logical,
        	optional IncludeMmonthN as logical,
        	optional IncludeMmonthT as logical,
        	optional IncludeMdayn as logical,
        	optional IncludeMdayT as logical
        	
) as table => let
 
  StartDate =  Start.Date,
  EndDate = if End.Date = null then DateTime.Date(DateTime.LocalNow()) else End.Date,
  NumberOfDays = Duration.Days( EndDate - StartDate )+1,
  Dates = List.Dates(StartDate, NumberOfDays, #duration(1,0,0,0)),
  tablelist = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
  DateName = Table.RenameColumns(tablelist,{{"Column1", "Date"}}),
  DateFormat = Table.TransformColumnTypes(DateName,{{"Date", type date}}),
  Jalali1 = Table.AddColumn(DateFormat, "jalali", each Date.ToText([Date], "yyyy-MM-dd", "fa-IR")),
  Jalali2 = if IncludeJalali2 <> false then Table.AddColumn(Jalali1, "jalali2", each Date.ToText([Date], "yyyy/MM/dd", "fa-IR")) else Jalali1,
  Jalali3 = if IncludeJalali3 <> false then Table.AddColumn(Jalali2, "jalali3", each Date.ToText([Date], "yyyy/M/d", "fa-IR")) else Jalali2,
  jdatekey = if IncludeJDateKey <> false then Table.AddColumn(Jalali3,"jdatekey", each Text.Remove([jalali], {"-"})) else Jalali3,
  jdatekey1 = if IncludeJDateKey1 <> false then Table.TransformColumnTypes(jdatekey,{{"jdatekey", Int64.Type}}) else jdatekey,
  jyear = if IncludeJYear <> false then Table.AddColumn(jdatekey1, "jyear", each Number.FromText(Text.Start([jalali],4)), Int64.Type) else jdatekey1,
  jmonthN = if IncludeJMonthN <> false then Table.AddColumn(jyear, "jmonthN", each Number.FromText(Text.End(Text.Start([jalali],7),2)),Int64.Type) else jyear,
  jdayn = if IncludeJDayN <> false then Table.AddColumn(jmonthN, "jdayn", each Text.End([jalali],2)) else jmonthN,
  jdayn1 = if IncludeJDayN1 <> false then Table.TransformColumnTypes(jdayn,{{"jdayn", Int64.Type}}) else jdayn,
  jmonthT = if IncludeJMonthT <> false then Table.AddColumn(jdayn1, "jmonthT", each Date.ToText([Date],"MMMM","fa-IR"), type text) else jdayn1,
  jHalfYear = if IncludeJHalfYear <> false then Table.AddColumn(jmonthT, "jHalfYear", each if [jmonthN]<=6 then "نیم سال اول" else "نیم سال دوم", type text) else jmonthT,
  jseasonT = if IncludeJSeasonT <> false then Table.AddColumn(jHalfYear, "jseasonT", each if List.Contains({1, 2, 3}, [jmonthN]) then "بهار" else if List.Contains({4, 5, 6}, [jmonthN]) then "تابستان" else if List.Contains({7, 8, 9}, [jmonthN]) then "پاییز" else "زمستان" , type text) else jHalfYear,
  JDayofYear = if IncludeJDayOfYear <> false then Table.AddColumn(jseasonT, "JDayofYear", each if Date.DayOfYear([Date])>=80 then Date.DayOfYear([Date])-79 else Date.DayOfYear([Date])+286,Int64.Type) else jseasonT,
  jdayT = if IncludeJDayT <> false then Table.AddColumn(JDayofYear, "jdayT", each Date.ToText([Date],"dddd","fa-IR"), type text) else JDayofYear,
        	YYYYMM = if IncludeYYYYMM <> false then Table.AddColumn(jdayT, "YYYY-MM", each Text.BeforeDelimiter([jalali], "-", 1), type text) else jdayT,
  QuarterN = if IncludeQuarterN <> false then Table.AddColumn(YYYYMM, "QuarterN", each if List.Contains({1, 2, 3}, [jmonthN]) then 1 else if List.Contains({4, 5, 6}, [jmonthN]) then 2 else if List.Contains({7, 8, 9}, [jmonthN]) then 3 else 4,Int64.Type ) else YYYYMM,
  Myear = if IncludeMyear <> false then Table.AddColumn(QuarterN, "Myear", each Date.Year([Date]), Int64.Type) else QuarterN,
  MmonthN = if IncludeMmonthN <> false then Table.AddColumn(Myear, "MmonthN", each Date.Month([Date]), Int64.Type) else Myear,
  MmonthT = if IncludeMmonthT <> false then Table.AddColumn(MmonthN, "MmonthT", each Date.ToText([Date],"MMMM"), type text) else MmonthN,
  Mdayn = if IncludeMdayn <> false then Table.AddColumn(MmonthT, "Mdayn", each Date.DayOfWeek([Date]), Int64.Type) else MmonthT,
  MdayT = if IncludeMdayT <> false then Table.AddColumn(Mdayn, "MdayT", each Date.ToText([Date],"dddd"), type text) else Mdayn
in
  MdayT,
 
Documentation = [
  Documentation.Name =  "DimDate Shamsi and miladi",
]
in
  Value.ReplaceType(DateShamsiandmiladi, Value.ReplaceMetadata(Value.Type(DateShamsiandmiladi), Documentation))

۳- انتخاب بازه زمانی جدول داینامیک تقویم شمسی

بعد از تأیید کد جایگذاری شده، صفحه به شکل زیر نمایش داده می‌شود. در این صفحه، شما می‌توانید با انتخاب بازه زمانی (تاریخ شروع و پایان) به ایجاد جدول داینامیک تقویم شمسی اقدام کنید.

برای تعیین تاریخ شروع و پایان بازه زمانی در جدول، باید به‌صورت تاریخ میلادی، روز موردنظر را در فیلد مربوطه در بخش Enter Parameters وارد کنید. به‌عنوان مثال، اگر بخواهید داده‌های جدول شمسی از ابتدای سال ۱۴۰۱ تا انتهای سال ۱۴۰۲ ایجاد شود، لازم است در فیلد Start.Date تاریخ معادل میلادی آن یعنی ۲۱/۳/۲۰۲۲ و در فیلد End.Date تاریخ ۱۹/۳/۲۰۲۴ انتخاب شود. شایان ذکر است اگر فیلد تاریخ پایان (End.Date) را خالی بگذارید، اسکریپت به‌صورت خودکار تاریخ روز جاری میلادی را درج می‌کند؛ به طوری که با هربار Refresh شدن گزارش، به‌صورت خودکار تاریخ روز جاری نیز به جدول اضافه و آپدیت می‌شود.

در این قسمت، به‌صورت پیش‌فرض تمامی ستون‌های درج شده به جدول اضافه می‌شوند. لذا در صورت عدم نیاز به ایجاد برخی ستون‌ها و حذف ستون‌هایی، کافی است در فیلد ستون موردنظر کلمه False را تایپ کنید. توجه کنید که بعد از ایجاد جدول، امکان ویرایش مجدد ستون‌ها (حذف و اضافه) وجود دارد.

۴- نهایی سازی ایجاد جدول تقویم شمسی در Power BI

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

۵- امکان ویرایش داینامیک بودن جدول

ویرایش و تغییر ویژگی داینامیک بودن جدول امکان‌پذیر است. برای ویرایش بازه زمانی و درج / حذف ستون‌های درج، می‌توان در بخش Query Settings روی گزینه تنظیمات Source کلیک کنید. حال در صفحه پاپ آپ بازشده، تنظیمات مدنظر خود را اعمال و سپس تأیید کنید.

۶- ایجاد ارتباط با سایر جداول

پس از ایجاد و ذخیره جدول تقویم شمسی، می‌توان به واسطه ایجاد روابط (Relationships) بین جداول از ستون‌های تاریخ میلادی در نرم افزار Power BI استفاده کرد.

جمع بندی: ساخت جدول داینامیک تقویم شمسی

برای استفاده از تقویم شمسی در Power BI روش‌هایی وجود دارند که در این مقاله به بررسی بهترین آن اشاره شد. شما می‌توانید با دنبال کردن گام‌های مذکور، به‌راحتی به این مزیت دسترسی داشته باشید. برای آشنایی بیشتر با این نرم افزار قدرتمند، پیشنهاد می‌کنیم مقاله جامع Power BI را نیز مطالعه کنید.