گزارش سررسید حساب‌های دریافتنی(Account-Receivable) در Power BI
گزارش سررسید حساب‌های دریافتنی در Power BI
گزارش سررسید حساب‌های دریافتنی (Accounts Receivable Aging) به شرکت‌ها کمک می‌کند تا مطالبات معوق را پیگیری کرده و جریان نقدی خود را بهتر مدیریت کنند. این گزارش یکی از نیازهای رایج بخش مالی است که معمولاً چالشی برای گزارش‌های Power BI محسوب می‌شود، به‌ویژه زمانی که بخواهید روند تغییرات را در طول زمان نمایش دهید.
در اینجا یک نمونه از تصویرسازی این گزارش را مشاهده می‌کنید که مبلغ باز (Open Amount) را در پایان هر ماه نمایش می‌دهد و بر اساس محدوده سنی حساب‌های دریافتنی رنگ‌بندی شده است.
این مقاله نشان می‌دهد که چگونه می‌توان یک راهکار منعطف و مقیاس‌پذیر برای گزارش سررسید حساب‌های دریافتنی (AR Aging) در Power BI پیاده‌سازی کرد و چگونه می‌توان یک مسئله کسب‌وکار را با استفاده از الگوهای استاندارد مدل‌سازی ساده‌تر کرد.
مدل داده منبع تقریباً هیچ‌وقت ایده‌آل نیست
اگرچه گزارش سررسید حساب‌های دریافتنی (AR Aging) یک استاندارد رایج در گزارش‌گیری مالی است، اما داده‌های منبع معمولاً در قالب‌هایی قرار دارند که نیازهای موردنظر را برآورده نمی‌کنند. این قالب‌ها شامل موارد زیر هستند:
یک جدول بزرگ: اگر داده‌ها به‌صورت یک فایل تخت یا یک جدول یکپارچه بدون روابط باشند، ایجاد یک گزارش تعاملی در Power BI چالش‌برانگیز خواهد بود.
سیستم‌های OLTP: پایگاه‌های داده رابطه‌ای مبتنی بر تراکنش که برای ورود داده‌ها بهینه شده‌اند، نه برای تحلیل. این منابع معمولاً شامل تعداد زیادی جدول و روابط پیچیده هستند که برای تولید نتایج تجمیعی، محاسبات سنگینی را نیاز دارند.
مراکز داده / انبارهای داده: ساختارهایی که برای گزارش‌گیری بهینه شده‌اند، اما گاهی جزئیات یا انعطاف‌پذیری لازم برای نمایش دقیق گزارش سررسید حساب‌های دریافتنی را ندارند.
این مدل‌های داده معمولاً ساختار ایده‌آلی برای گزارش‌های AR Aging فراهم نمی‌کنند و مشکلات رایجی مانند موارد زیر را ایجاد می‌کنند:
پیوستگی‌های پیچیده یا عدم وجود روابط که ردیابی وضعیت فاکتورها یا پرداخت‌های جزئی را دشوار می‌کند.
توانایی محدود در مدیریت اعتبارات جزئی، پرداخت‌های معوق یا تعدیلاتی که در طول زمان اتفاق می‌افتند.
چالش در تطبیق قوانین خاص کسب‌وکار (مانند دسته‌بندی‌های مختلف سررسید، تبدیل ارزها یا شرایط تخفیف) با داده‌های خام تراکنشی.
بنابراین، وارد کردن مستقیم داده‌ها معمولاً نیازمند ایجاد معیارهای پیچیده DAX برای تولید نتایج موردنظر است. همچنین، ایجاد پنج گزارش مشابه برای پنج مشتری مختلف اغلب به پنج مدل و معیار کاملاً متفاوت نیاز دارد.
برای جلوگیری از این مشکلات، ابتدا داده‌ها را به مدلی تبدیل می‌کنیم که بهتر با نیازهای گزارش سررسید حساب‌های دریافتنی همخوانی داشته باشد. می‌توانیم از الگوهای استاندارد مدل‌سازی استفاده کنیم تا فرایند را ساده‌تر یا حداقل استانداردسازی کنیم، به‌ویژه زمانی که به یک ساختار داده‌ای مناسب دست پیدا کنیم.
مدل‌سازی مسئله در Power BI
برای ایجاد یک گزارش سررسید حساب‌های دریافتنی (AR Aging) کارآمد، ابتدا داده‌های منبع را به شکلی تبدیل می‌کنیم که امکان تجزیه‌وتحلیل ساده‌تر را فراهم کند. در اینجا، داده‌ها از سه جدول تشکیل شده‌اند:
فاکتورها (Invoices): شامل جزئیات فاکتورها، مشتری، تاریخ صدور، مبلغ و شرایط پرداخت.
سررسید پرداخت‌ها (Payments Due): شامل تاریخ سررسید و مبلغ پرداختی برای هر فاکتور.
پرداخت‌ها (Payments): شامل پرداخت‌های انجام‌شده، تاریخ و مبلغ پرداختی مربوط به هر فاکتور.
ساختار داده‌های منبع
جدول فاکتورها (Invoices):
| مشتری | فاکتور | تاریخ | مبلغ | شرایط پرداخت |
|---|---|---|---|---|
| Litware | INV-001 | 2024/09/15 | 10,000 | 30 |
| Litware | INV-002 | 2024/09/15 | 16,000 | 60/90 |
| Contoso | INV-003 | 2024/09/15 | 3,000 | 15 |
جدول سررسید پرداخت‌ها (Payments Due):
| مشتری | فاکتور | تاریخ سررسید | مبلغ سررسید |
|---|---|---|---|
| Contoso | INV-003 | 2024/09/30 | 3,000 |
| Litware | INV-001 | 2024/10/15 | 10,000 |
| Litware | INV-002 | 2024/11/15 | 8,000 |
| Litware | INV-002 | 2024/12/15 | 8,000 |
جدول پرداخت‌ها (Payments):
| مشتری | فاکتور | تاریخ پرداخت | مبلغ پرداختی |
|---|---|---|---|
| Contoso | INV-003 | 2024/09/30 | 3,000 |
| Litware | INV-001 | 2024/10/30 | 7,000 |
| Litware | INV-001 | 2024/12/30 | 3,000 |
| Litware | INV-002 | 2024/12/30 | 12,000 |
| Litware | INV-002 | 2025/01/30 | 2,000 |
چالش‌ها در مدل‌سازی
داده‌های پرداخت ممکن است به یک فاکتور خاص اشاره نکنند، بنابراین در برخی موارد باید پرداخت‌ها را بر اساس تاریخ سررسید تخصیص دهیم.
استفاده از DAX برای تطبیق پرداخت‌های دریافت‌شده با سررسیدها در زمان اجرا می‌تواند منجر به پیچیدگی، خطا و کاهش کارایی شود.
بهینه‌سازی ساختار داده برای امکان استفاده مجدد در مدل‌های دیگر ضروری است.
ایجاد جدول AR Detail
برای ساده‌سازی تحلیل و کاهش پیچیدگی مدل، بهتر است یک جدول «واقعی» (Fact Table) برای حساب‌های دریافتنی ایجاد کنیم که شامل تمام جزئیات پرداخت‌های تخصیص‌یافته به سررسیدها باشد:
| مشتری | فاکتور | مبلغ سررسید | تاریخ سررسید | مبلغ پرداختی | تاریخ پرداخت |
|---|---|---|---|---|---|
| Contoso | INV-003 | 3,000 | 2024/09/30 | 3,000 | 2024/09/30 |
| Litware | INV-001 | 7,000 | 2024/10/15 | 7,000 | 2024/10/30 |
| Litware | INV-001 | 3,000 | 2024/10/15 | 3,000 | 2024/12/30 |
| Litware | INV-002 | 8,000 | 2024/11/15 | 8,000 | 2024/12/30 |
| Litware | INV-002 | 4,000 | 2024/12/15 | 4,000 | 2024/12/30 |
| Litware | INV-002 | 2,000 | 2024/12/15 | 2,000 | 2025/01/30 |
| Litware | INV-002 | 2,000 | 2024/12/15 | – | – |
این جدول دارای دانه‌بندی (Granularity) در سطح هر پرداخت مرتبط با هر سررسید است، بنابراین هر پرداخت و سررسید در یک ردیف مجزا نمایش داده می‌شود.
گام‌های بعدی
برای ساخت این جدول، می‌توان از فرآیندهای ETL در Power Query استفاده کرد. ایجاد یک جدول واسط نرمال‌سازی‌شده برای تبدیل آسان داده‌های خام به AR Detail به ما امکان می‌دهد که این فرآیند را در مدل‌های دیگر نیز مجدداً استفاده کنیم.
نرمال‌سازی داده‌های تراکنشی (Movements Table)
همانطور که مشاهده کردیم، داده‌های منبع معمولاً شامل جداول جداگانه‌ای برای فاکتورها و پرداخت‌ها هستند. هرکدام از این جداول می‌توانند دارای ستون‌ها و روابط متفاوتی باشند که باعث پیچیدگی تحلیل می‌شود. برای ساده‌سازی این فرآیند، یک جدول نرمال‌شده با نام Movements ایجاد می‌کنیم که همه این تراکنش‌ها را در یک ساختار استاندارد ادغام کند.
ساختار جدول Movements
| مشتری | سند | نوع تراکنش | مبلغ | تاریخ |
|---|---|---|---|---|
| Litware | INV-001 | Invoice | 10,000 | 2024/09/15 |
| Litware | INV-001 | Payment Due | 10,000 | 2024/10/15 |
| Litware | INV-001 | Payment | 7,000 | 2024/10/30 |
| Litware | INV-001 | Payment | 3,000 | 2024/12/30 |
| Litware | INV-002 | Invoice | 16,000 | 2024/09/15 |
| Litware | INV-002 | Payment Due | 8,000 | 2024/11/15 |
| Litware | INV-002 | Payment Due | 8,000 | 2024/12/15 |
| Litware | INV-002 | Payment | 12,000 | 2024/12/30 |
| Litware | INV-002 | Payment | 2,000 | 2025/01/30 |
| Contoso | INV-003 | Invoice | 3,000 | 2024/09/15 |
| Contoso | INV-003 | Payment Due | 3,000 | 2024/09/30 |
| Contoso | INV-003 | Payment | 3,000 | 2024/09/30 |
چرا جدول Movements مهم است؟
ساختار یکپارچه: این جدول تمامی اطلاعات مربوط به فاکتورها، پرداخت‌های سررسید شده و پرداخت‌های انجام‌شده را در یک قالب استاندارد ذخیره می‌کند.
ساده‌سازی مدل‌سازی: به جای استفاده از چندین جدول مرتبط، می‌توان از یک جدول واقعی (Fact Table) در مدل ستاره‌ای (Star Schema) استفاده کرد.
تحلیل انعطاف‌پذیر: این ساختار امکان تجزیه‌وتحلیل پرداخت‌ها و فاکتورها را در طول زمان، بدون نیاز به نگارش توابع پیچیده DAX، فراهم می‌کند.
قابلیت استفاده مجدد: با تطبیق مدل داده با این ساختار، می‌توان از مدل‌ها و اندازه‌گیری‌های مشابه در سناریوهای مختلف استفاده کرد.
گام‌های بعدی
در این مقاله، فرآیند تبدیل داده‌های خام به جدول Movements توضیح داده نشده است، اما در فایل‌های نمونه، دو راهکار ارائه شده است:
AR Aging – sample: شامل یک نمونه از جدول Movements با محتوای بالا و چندین سند اضافی.
AR Aging – volume: این راهکار با استفاده از جدول پارامترهای پنهان (Hidden Parameters)، حجم داده‌ها را افزایش می‌دهد تا عملکرد مدل و بهینه‌سازی‌های انجام‌شده ارزیابی شوند.
نتیجه‌گیری
با استفاده از جدول Movements، می‌توان گزارش‌های متنوعی را بدون نیاز به نوشتن هرگونه فرمول DAX ایجاد کرد. با این حال، گزارش سررسید حساب‌های دریافتنی (AR Aging) به تلاش بیشتری نیاز دارد. خبر خوب این است که اگر مدل داده‌ای خود را مطابق با این ساختار تنظیم کنید، می‌توانید از مدل‌ها و فرمول‌های DAX ارائه‌شده در ادامه مقاله برای ایجاد یک راهکار کامل AR Aging استفاده کنید.
پیاده‌سازی جزئیات حساب‌های دریافتنی از حرکات نرمال‌شده
چگونه می‌توان جدول جزئیات حساب‌های دریافتنی (AR Detail) که در ابتدای مقاله توضیح دادیم را از جدول حرکات (Movements) به دست آورد؟ شما می‌توانید از هر ابزار تبدیل داده‌ای که با آن راحت‌تر هستید، استفاده کنید. به عنوان مثال، می‌توان این تبدیل را در SQL یا Power Query پیاده‌سازی کرد. یک راه‌حل پیچیده می‌تواند جدول جزئیات حساب‌های دریافتنی را به‌صورت تدریجی پر کند و زمان پردازش هر به‌روزرسانی را کاهش دهد.
لازم است تأکید کنیم که این تبدیل ضروری است، زیرا به‌ندرت پیش می‌آید که منبع داده دقیقاً دارای ساختار و دانه‌بندی مورد نیاز برای جزئیات حساب‌های دریافتنی باشد. بنابراین، این نوع گزارش‌دهی در محیط‌های بلادرنگ (real-time) به خوبی کار نمی‌کند، زیرا باید زمان تأخیر (latency) لازم برای این تبدیل را در نظر گرفت.
اگر حجم داده‌های شما نسبتاً کم باشد، می‌توانید از جدول محاسباتی در DAX استفاده کنید که در این مقاله توضیح داده شده است. مزیت این روش این است که جدول جزئیات حساب‌های دریافتنی همیشه با سایر جداول مدل همگام خواهد بود. ما انتظار داریم این جدول به عنوان یک ویژگی اضافی از مدل معنایی موجود باشد، نه یک مدل معنایی جداگانه که فقط برای این هدف ساخته شده است.
برای تعریف حجم داده‌های کم، می‌توان فرض کرد که تعداد کمتر از ۱ میلیون پرداخت سررسید شده قابل‌قبول است. در مقابل، اگر بیش از ۱۰ میلیون پرداخت سررسید شده دارید، بهتر است روش‌های جایگزین را بررسی کنید که امکان به‌روزرسانی افزایشی این جدول را فراهم می‌کنند.
ما در این مقاله پیاده‌سازی DAX برای جدول جزئیات حساب‌های دریافتنی را به‌طور جزئی توضیح نمی‌دهیم. در واقع، هدف ما یافتن کارآمدترین پیاده‌سازی نبوده است، بلکه سعی کردیم محاسبات را به چندین مرحله تقسیم کنیم و از متغیرها (variables) استفاده کنیم تا بتوانید نحوه کار این کد را بهتر درک کنید.
جدول جزئیات حساب‌های دریافتنی (AR Detail) یک جدول واقعیت (Fact Table) در یک طرح ستاره‌ای (Star Schema) است که به جدول مشتری (Customer) و جدول تاریخ (Date) متصل می‌شود.
جدول بازه‌بندی سررسید (Aging Range) نیز بازه‌های سنی (Aging Buckets) را برای گزارش مشخص می‌کند، که در ادامه مقاله توضیح خواهیم داد.
در این حالت، روابط متعددی با تاریخ (Date) مشاهده می‌کنیم، زیرا هر ردیف در جدول جزئیات حساب‌های دریافتنی (AR Detail) سه تاریخ دارد: تاریخ فاکتور، تاریخ سررسید، و تاریخ پرداخت. دو تاریخ آخر (تاریخ سررسید و تاریخ پرداخت) هستند که برای اندازه‌گیری سن هر ردیف در جدول جزئیات حساب‌های دریافتنی استفاده می‌کنیم، همانطور که در بخش بعدی توضیح خواهیم داد.
استفاده از الگوهای DAX
ما دو نیاز اصلی برای ایجاد گزارش سن حساب‌های دریافتنی (AR Aging) داریم. اولین نیاز این است که باید مبلغ “سررسید” را در یک تاریخ خاص بدست آوریم. به این مبلغ، “مبلغ باز” (Open Amount) گفته می‌شود. هر ردیف در جدول جزئیات حساب‌های دریافتنی (AR Detail) می‌تواند همان مبلغ سررسید را برای هر تاریخ گزارشی بین تاریخ سررسید و تاریخ پرداخت پیش‌بینی کند.
ما باید مقداری کد DAX برای این منظور بنویسیم، اما نیازی به اختراع مجدد چرخ نداریم. این مشکل فقط یکی از موارد الگوی رویدادهای در حال پیشرفت (Events in Progress) است. شما می‌توانید از لینک مربوطه برای مطالعه نحوه عملکرد این محاسبه استفاده کنید. در اینجا کد آن به صورت سازگار با جزئیات حساب‌های دریافتنی (AR Detail) برای محاسبه مبلغ باز (Open Amount) در تاریخ گزارش‌دهی آورده شده است:
ما تنها نیمه‌راه برای دریافت گزارش سن حساب‌های دریافتنی (AR Aging) هستیم. در واقع، باید سن هر مبلغ را محاسبه کرده و آن را در بازه مربوطه تخصیص دهیم، با استفاده از ستون بازه (Range) از جدول بازه‌بندی سررسید (Aging Range) در گزارش خود. این محتوای بازه‌بندی سررسید (Aging Range) است، که ممکن است آن را از یک منبع خارجی (مثل یک جدول SharePoint) وارد کنیم تا حداکثر انعطاف‌پذیری را برای کاربران فراهم کنیم.
بار دیگر، ما از یک الگوی موجود دیگر، بخش‌بندی دینامیک (Dynamic Segmentation) استفاده می‌کنیم. به دلیل دلایل عملکردی، محاسبه را در یک معیار مبلغ باز (Open Amount) واحد ادغام می‌کنیم تا اجرای کارآمدتری داشته باشیم و با کاهش تعداد تغییرات زمینه (context transitions)، کارایی را افزایش دهیم:
شما می‌توانید درستی محاسبه را با دستکاری داده‌ها در فایل “AR Aging – sample.pbix” که در فایل ZIP قابل دانلود قرار دارد، آزمایش کنید: محتوای جدول محاسبه‌شده Movements را تغییر دهید تا موارد خاصی که ممکن است بخواهید در نظر بگیرید را آزمایش کنید و محاسبه را قبل از استفاده از مدل برای سناریوی خاص خود تایید کنید.
برای بررسی عملکرد، باید از فایل “AR Aging – volume.pbix” استفاده کنید. مقادیر اختصاص داده‌شده به متغیرهای _Customers و _Invoices در جدول Parameters را افزایش دهید. در حالی که ما فایل نمونه با 1,000 فاکتور را ارائه کرده‌ایم، اگر از 1,000,000 به عنوان تعداد فاکتورها استفاده کنید، ممکن است برای تازه‌سازی جداول محاسبه‌شده تا یک دقیقه صبر کنید. در این مرحله، نموداری که در ابتدای مقاله نشان داده‌ایم ممکن است نیاز به زمان زیادی برای تازه‌سازی داشته باشد. اگر حجم داده‌های شما به اندازه مشابهی برسد، ممکن است بخواهید یک گام اضافی برای بهبود عملکرد پرس‌و‌جو در نظر بگیرید. یک بخش خاص به بهینه‌سازی الگوی رویدادهای در حال پیشرفت (Events in Progress) اختصاص داده شده است که بر اساس یک جدول Snapshot است که می‌توانید آن را به روش‌های مختلف ایجاد کنید (همانطور که معمولاً می‌دانید، آماده‌سازی داده‌های خارجی بهتر است زیرا به‌روزرسانی‌های تدریجی را امکان‌پذیر می‌سازد). یکی از روش‌ها استفاده از یک جدول محاسبه‌شده DAX است:
جدول Snapshot جزئیات حساب‌های دریافتنی (AR Detail Snapshot) می‌تواند به جدول بازه‌بندی سررسید (Aging Range) متصل شود، زیرا این جدول پیش از محاسبه سن حساب‌های دریافتنی برای هر تاریخ گزارشی، سن مربوطه را محاسبه کرده و بازه مربوطه را در ستون AgeMin ذخیره می‌کند.
مژری که از جدول Snapshot استفاده می‌کند، با پیاده‌سازی ساده‌تری همراه است و اجرای آن بسیار سریع‌تر است:
گزارش سن حساب‌های دریافتنی (AR Aging) یک سناریو رایج است که می‌تواند به‌طور استاندارد از یک ساختار داده نرمال‌شده شناخته‌شده حل شود. ما جدول اولیه Movements را پیاده‌سازی کردیم که فرآیند ایجاد مدلی که بهتر با نیاز تحلیلی برای AR Aging سازگار است، استاندارد می‌کند: جدول AR Detail باید دارای دقت مناسب باشد تا محاسبه دینامیک سن هر ردیف برای هر تاریخ گزارش‌دهی خاص را ساده کند. با استفاده از الگوهای DAX شناخته‌شده و بهینه‌شده، ما تلاش‌های توسعه را به حداقل می‌رسانیم و ریسک خطاهای پیاده‌سازی را کاهش می‌دهیم. همچنین از بهینه‌سازی عملکرد که قبلاً برای چنین مدل‌هایی در دسترس است، بهره می‌بریم.
در حالی که این مقاله راه‌حل یک سناریوی خاص را شرح می‌دهد، پیشنهاد می‌کنیم از همان رویکرد هنگام مواجهه با یک نیاز تجاری جدید استفاده کنید: همیشه سعی کنید مشکل را به یک فهرست از مراحل کوچک‌تر تبدیل کنید که امکان استفاده مجدد از الگوهای عمومی را فراهم می‌کند.
دیدگاهتان را بنویسید