محاسبه MTD، QTD، YTD در Power BI برای دوره جاری
این مقاله توضیح می‌دهد که چگونه از محاسبات DAX برای تحلیل زمان استفاده کنید که برای آخرین دوره موجود در داده‌ها، که به آن دوره “جاری” گفته می‌شود، اعمال می‌شوند. توابع هوش زمان در DAX مانند ماه تا به امروز (MTD)، سه ماهه تا به امروز (QTD)، و سال تا به امروز (YTD) نسبت به متن فیلتر جاری عمل می‌کنند. نتیجه آن‌ها به فیلتر اعمال‌شده بستگی دارد، که این ویژگی باعث می‌شود این توابع برای مقایسه‌های مختلف و دوره‌های مختلف قابل تنظیم باشند. با این حال، اگر بخواهید جدیدترین داده‌ها را برای “دوره جاری” نشان دهید، مشکلی وجود دارد: بدون فیلتر مناسب، ممکن است داده‌هایی که به دنبالش هستید را به‌دست نیاورید.
نمایش نتایج دلخواه
برای مثال، گزارش زیر را در نظر بگیرید: مقادیری که در سطح ماه در گزارش مه ۲۰۱۹ نمایش داده می‌شود، محاسبات MTD را از ۱ مه ۲۰۱۹ نشان می‌دهند. این محاسبه هر ماه تکرار می‌شود. از آنجا که مه ۲۰۱۹ متعلق به سه‌ماهه دوم سال ۲۰۱۹ (Q2) است، محاسبه QTD از ۱ آوریل ۲۰۱۹ شروع می‌شود و محاسبه YTD از ۱ ژانویه ۲۰۱۹ آغاز می‌شود.
اگر ادامه مقاله را هم دارید، خوشحال می‌شوم که برای شما ترجمه کنم.
با این حال، آخرین داده موجود در نمونه استفاده‌شده مربوط به ۱۱ مارس ۲۰۲۰ است، زمانی که آخرین تحویل موجود است.
بنابراین، می‌توانیم فرض کنیم که ۱۱ مارس ۲۰۲۰ آخرین تاریخ موجود است یا به عبارت دیگر “دیروز”. یک نیاز رایج در داشبوردها، نمایش داده‌ها برای دوره زمانی “جاری” است: محاسبه MTD معادل مقدار ماه “جاری” است، محاسبه QTD معادل سه‌ماهه “جاری” است، و محاسبه YTD معادل سال “جاری” است.
شما می‌توانید مشاهده کنید که اعداد گزارش‌شده در ردیف اول تصویر کارت (همه مربوط به مقدار فروش) معادل مقادیر نمایش‌داده‌شده در اسکرین‌شات قبلی هستند که داده‌ها را برای مارس ۲۰۲۰ در یک ماتریس نمایش می‌دهند. تصویر کارت (یا در برخی موارد، کل صفحه) باید با استفاده از تاریخ آخرین تراکنش از جدول Sales فیلتر شود تا نتایج مورد نظر را نمایش دهد. برای دستیابی به این هدف، می‌توانیم یک فیلتر به تصویر (یا به کل صفحه) اعمال کنیم که تنها تاریخ ۳ مارس ۲۰۲۰ را فیلتر کند – چرا که این تاریخ آخرین تراکنش است. با این حال، انتخاب دستی این تاریخ کار نخواهد کرد اگر در بروزرسانی بعدی داده‌ها، آخرین تاریخ موجود به ۱۲ یا ۱۳ مارس تغییر کند. بنابراین، باید یک فیلتر “ثابت” اعمال کنیم که در روزهای بعدی به طور خودکار فیلتر مؤثر را به‌روزرسانی کند.
تنظیم متن فیلتر صحیح
یک رویکرد رایج استفاده از Slicer تاریخ نسبی در Power BI است. با تنظیم “امروز” یا “این ماه”، شما یک انتخاب دریافت می‌کنید که تاریخ جاری را به درستی به عنوان فیلتر اعمال می‌کند.
با این حال، این روش محدودیت‌های زیادی دارد:
با استفاده از “امروز”، اگر داده‌ها تا تاریخ روز قبل به‌روزرسانی شوند، هیچ داده‌ای برای تاریخ جاری نمایش داده نمی‌شود. در این حالت، ممکن است بخواهید از “آخرین ۲ روز” استفاده کنید، اما این در روز دوشنبه که هیچ تراکنشی در آخر هفته ثبت نشده است، به خوبی کار نمی‌کند.
با استفاده از “این ماه”، اگر مدل هر شب به‌روزرسانی شود، در اولین روز ماه هیچ داده‌ای نمایش داده نمی‌شود. در این صورت، ممکن است بخواهید مقادیر ماه گذشته را تحلیل کنید، زیرا هنوز هیچ داده‌ای برای ماه جاری ندارید.
با استفاده از “این ماه”، شما یک تاریخ حداکثر را که معادل آخرین روز ماه است، تعیین می‌کنید. این فیلتر ممکن است مقایسه ماه به تاریخ با ماه گذشته به تاریخ را مختل کند، که باید فقط ۱۴ روز از ماه گذشته را در نظر بگیرد اگر ماه جاری فقط ۱۴ روز داده داشته باشد – مگر اینکه الگوی محاسباتی DAX مناسب را پیاده‌سازی کرده باشید.
به طور کلی، انتخاب یک تاریخ مبتنی بر تابع TODAY در DAX به عنوان تاریخ مرجع و سپس اعمال یک فیلتر بر روی ستون Date[Date]، محدودیت‌هایی را که در بالا برای Slicer تاریخ نسبی توضیح داده شد، ایجاد می‌کند. شروع یک ماه جدید را در نظر بگیرید: اگر شما در اولین روز ماه باشید، احتمالاً می‌خواهید داده‌های آخرین روز ماه گذشته را مشاهده کنید. اما نه هر آخرین روزی – ممکن است بخواهید دقیقاً آخرین روز کاری یا حتی آخرین روز با تراکنش‌ها در جدول Sales را ببینید. این نکته به اهمیت نیاز به یک روش دقیق‌تر اشاره دارد.
روش‌های پیشنهادی
به همین دلایل، دو روش ممکن را در نظر می‌گیریم: یکی مبتنی بر یک ستون محاسبه‌شده در جدول Date و دیگری مبتنی بر یک گروه محاسباتی.
استفاده از یک ستون محاسبه‌شده در جدول Date
با ایجاد یک ستون محاسبه‌شده به نام CurrentDate در جدول Date، می‌توانیم به طور مؤثری آخرین تاریخ با داده‌های تراکنشی را با یک رشته خاص شناسایی کنیم: LastDateWithData. می‌توانیم با انتخاب این مقدار برای ستون، فیلتر تصویر یا صفحه را اعمال کنیم. مقدار LastDateWithData در هر به‌روزرسانی به تاریخ صحیح منتقل می‌شود و به مرجع درست برای توابع هوش زمانی ما تبدیل می‌شود.
ستون محاسبه‌شده CurrentDate به ستون DateWithTransaction وابسته است، که شما به طور خودکار در جدول Date تولید شده توسط Bravo for Power BI به‌دست می‌آورید. اگر از جدول Date دیگری استفاده می‌کنید، می‌توانید ستون DateWithTransaction را با افزودن ستون محاسبه‌شده زیر به‌دست آورید:
فرمول برای ستون محاسبه‌شده CurrentDate به شرح زیر است:
متغیر LastDateWithData حداکثر تاریخ (MAX(‘Date'[Date])) را که تراکنش‌ها وجود دارند (‘Date'[DateWithTransactions] = TRUE) برمی‌گرداند. تابع REMOVEFILTERS تضمین می‌کند که این محاسبه بر روی تمام جدول Date انجام شود و تغییر متن فیلتر ناشی از تابع CALCULATE در ستون محاسبه‌شده نادیده گرفته شود.
پس از آن که متغیر LastDateWithData تاریخ آخرین تراکنش را در خود نگه داشت، دستور RETURN هر ردیف از جدول Date را بررسی می‌کند تا ببیند تاریخ آن با LastDateWithData تطابق دارد یا خیر. اگر تطابقی وجود داشته باشد، آن ردیف را با “LastDateWithData” علامت‌گذاری می‌کند. در اصل، این ستون محاسبه‌شده به‌عنوان یک پرچم عمل می‌کند که نشان می‌دهد کدام تاریخ در جدول Date به تاریخ آخرین تراکنش مربوط می‌شود.
داشتن این ستون محاسبه‌شده در جدول Date بسیاری از عملیات‌های بعدی را ساده می‌کند. به عنوان مثال، زمانی که می‌خواهید یک تصویر را فیلتر کنید تا فقط داده‌ها را تا تاریخ آخرین تراکنش نمایش دهد، می‌توانید با مقدار “LastDateWithData” در ستون CurrentDate فیلتر کنید. این فیلتر را می‌توان در پنل فیلتر برای یک تصویر، تمام تصاویر در یک صفحه، یا کل گزارش اعمال کرد.
این فیلتر تضمین می‌کند که محاسبات MTD، QTD و YTD همیشه مقادیر را تا آخرین تراکنش منعطف کنند و به طور خودکار آخرین دوره موجود در داده‌ها را در نظر بگیرند.
استفاده از یک گروه محاسباتی
یک گروه محاسباتی می‌تواند تمام اندازه‌گیری‌ها را قطع کرده و همان فیلتر را که در مثال قبلی با فیلتر کردن یک ستون محاسبه‌شده در جدول Date اعمال کردیم، اعمال کند. در حالی که گروه محاسباتی می‌تواند یک پوشش برای ستون محاسبه‌شده‌ای باشد که قبلاً تعریف کردیم، می‌توانیم تمام منطق کسب‌وکار را درون آیتم محاسبه پیاده‌سازی کنیم به طوری که گروه محاسباتی وابسته به تغییرات اضافی اعمال‌شده در مدل داده نباشد.
در Power BI Desktop، می‌توانیم یک گروه محاسباتی را با استفاده از دکمه Calculation group در بخش محاسبات در ریبون که هنگام فعال‌سازی نمای مدل (Model view) قابل مشاهده است، ایجاد کنیم.
ما یک گروه محاسباتی Current Period ایجاد می‌کنیم و از همان نام (Current Period) برای ستون با فهرست آیتم‌های محاسباتی استفاده می‌کنیم. اولین آیتم محاسبه‌ای که تعریف می‌کنیم، آخرین تاریخ با داده است:
متغیر LastDateWithData حداکثر تاریخ موجود در Sales[Order Date] و Sales[Delivery Date] را زمانی که تراکنش‌ها وجود دارند (‘Date'[DateWithTransactions] = TRUE) برمی‌گرداند. تابع REMOVEFILTERS تضمین می‌کند که محاسبه تمام ردیف‌ها در Sales را در نظر بگیرد، بدون توجه به هر فیلتر خارجی.
با شناسایی تاریخ آخرین تراکنش، فرمول سپس اندازه‌گیری انتخاب‌شده کنونی (SELECTEDMEASURE) را تحت زمینه این تاریخ مجدداً محاسبه می‌کند. زمانی که این آیتم محاسبه‌ای را به‌عنوان فیلتر به یک تصویر اعمال می‌کنید، تمام اندازه‌گیری‌ها از تاریخ آخرین تراکنش به‌عنوان زمینه فیلتر استفاده می‌کنند، بنابراین YTD، QTD و MTD از آن تاریخ به‌عنوان مرجع استفاده می‌کنند.
گروه محاسباتی می‌تواند آیتم‌های محاسباتی اضافی برای انتخاب‌های مختلف داشته باشد. به عنوان مثال، ماه گذشته با داده کل ماهی را انتخاب می‌کند که شامل آخرین تاریخ با داده است:
تابع REMOVEFILTERS ضروری است زیرا ما یک فیلتر بر روی ستونی غیر از Date[Date] اعمال می‌کنیم که باعث حذف خودکار فیلتر هنگام فیلتر شدن می‌شود.
اگر ستون Year Month Number در جدول Date ندارید، می‌توانید این نسخه عمومی‌تر را که فقط به جدول Date وابسته است، پیاده‌سازی کنید:
به طور مشابه، می‌توانیم آیتم محاسبه‌ای آخرین ربع با داده را با اتکا به ستون Date[Year Quarter Number] پیاده‌سازی کنیم:
نسخه‌ای که به ستون‌های خاص در جدول Date وابسته نیست، از QUARTER به جای MONTH در تابع PARALLELPERIOD استفاده می‌کند:
در حالی که توابع هوش زمانی در DAX انعطاف‌پذیری و قدرت زیادی ارائه می‌دهند، سفارشی‌سازی آن‌ها برای آخرین دوره می‌تواند چالش‌برانگیز باشد. برای غلبه بر این مشکل، می‌توان از ترکیبی از دستکاری زمینه فیلتر و یک ستون محاسبه‌شده یا گروه محاسباتی استفاده کرد.
با پیاده‌سازی این استراتژی‌ها، می‌توانید از همان محاسبات هوش زمانی برای گزارش‌ها و داشبوردهای مرتبط با زمان استفاده کنید که اطلاعات مربوط به دوره زمانی اخیر را نمایش می‌دهند.
دیدگاهتان را بنویسید