Static end of month day

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.

Customer Code/ Customer Name/ Invoice Date/Invoice number/Ageing Days/1-30 days/30-60days/60-90days/90days+

Hi @Victoria.

To help us further analyze your current state and visualize your issue, could you please provide as many as you can of:

  • Your work-in-progress PBIX file, using sanitized data if necessary
  • Your dataset as an Excel file (again, sanitized if necessary)
  • A detailed mock-up (marked-up screenshot of the visual in question or Excel file) showing your desired outcome.

Also, if you provide DAX or Power Query code in your post, please format it using the built-in formatter.

Greg

_eDNA Forum - Format DAX or PQ

1 Like

Hi @Victoria ,

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:

  1. table InvoiceT with Customer Name, Invoice Date, and Value of the invoice
  2. table Aged Debtor Groups with the aging groups from D1 to 30, 30 to 60, 60 to 90 and > 90D
  3. 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)
  4. 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.

Kind regards, Jan van der Wind

The two EDNA videos, used from Sam McKay,

3 Likes

Amazing support here. Nice one

3 Likes

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.

Hi @Victoria, v2

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])),
image

Hope that helps you further,
Kind regards, Jan van der Wind

Hi Greg, I tried to send you my file but the email you used is no reply so bounced back…

Sorry I didn’t use the forum before.

Hi @Victoria. Use the upload button shown in the image in my earlier post in this thread to send your PBIX.
Greg

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.

Sorry for the late reply, I was trying to solve the issue on my own. Unfortunately none of the answers are what I am after…