Hi everyone, I am trying to build an Aged Debtor report as at end of month.
I followed Sam’s tutorial which is great but I need one report which has a static end of month. What I need to show is as per below , so for example the Ageing days should be the day of invoice until the date of the month end when the report is generated example 31.05.2021.
I tried to use end of month formula but I think I have to many periods, my date table is up to 2030.Question : How can I delete the dates and update on a monthly basis the date table?
If I have a date table would I be able to use and of month formula and achieve a static date or someone can help me with a formula please, Many thanks.
Please receive hereby a workout, based upon the two EDNA videos of AR reporting, and using the Power BI EDNA “Aged Trial Balance” report, which is adjusted to your request, to report Debtor Aging dynamically as per the end of the month.
Needed:
table InvoiceT with Customer Name, Invoice Date, and Value of the invoice
table Aged Debtor Groups with the aging groups from D1 to 30, 30 to 60, 60 to 90 and > 90D
table Month Periods which contains of a range of 13 periods, from -12 to 0, representing the lookback monthly periods, which is created by Power BI using a parameter (Modeling → New Parameter)
Measure group “Accounts Receivable”, the upmost DAX formulas already made by EDNA
There is no need for a Date Table at all for this workout with dynamic EOM-periods, .
The Month End Period is calculated in the measure “Selected Date” , which can be selected in the report with the slicer: Period 0 is defined as Today, periods -12 till -1 are months backwards at the end of the month, the formula is stated below, the corresponding date of the selected period is projected in the report:
Selected Date = if( [MonthPeriodNr] = 0, TODAY(), EOMONTH(TODAY(),[MonthPeriodNr]))
As Today() increases every day, the Lookback period changes automatically with the change of time. If you would like to see more past periods, just increase the negative number at the measure “Month End Periods” [ MonthPeriods = GENERATESERIES(-12, 0, 1) ]
Please find attached PBIX for further details, and an example of how the report looks like below. Debtor Aging reporting v4.pbix (355.5 KB)
If you have further questions or remarks, please let me know.
I think my issue is I have to many periods open like 2030 .How do I delete them to have until now and then how do I update the date table on a monthly basis?
Many thanks for all the replies. Much appreciate your support.
Create with the standard date query in Power Query Editor a new date table with the desired period, and replace the existing date table.
Alternatively an modified Date Table, with only month end dates could be used as attached, see PBIX , (not a fan of those kind of solutions) MonthEnd DateTable.pbix (66.7 KB)
Adjusted M-code: Source = List.Dates(StartDate,DayCount/31,#duration(31,0,0,0)), EOMDatum = Table.AddColumn(InsertYear,“EoMonth”, each Date.EndOfMonth([Date])),
Hope that helps you further,
Kind regards, Jan van der Wind
Hi @Victoria, did the response provided by @Greg help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.
I hope that you are having a great experience using the Support Forum so far. Kindly take time to answer the Enterprise DNA Forum User Experience Survey, we hope you’ll give your insights on how we can further improve the Support forum. Thanks!
Hi @Victoria, we’ve noticed that no response has been received from you since the 16th of June. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.
Hi @Victoria, due to inactivity, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Thanks.
