Hello,
I am having trouble with one of my reports that compares balance sheet accounts for the current financial period vs. prior financial period. The issue I’m running into is the the prior current period formula isn’t calculating to the last day of the prior month. It only seems like it happens when the current month has 30 days and the previous month had 31 days. Any help would be sincerely appreciated!!
This is the formula that we are using:
Regards,
Brandon McIlhargey
Best answer by WillH
Hi
I just tested, I’m pretty sure the below code works:
=Format('{0:MM/dd/yyyy}',DateAdd(Report.GetPeriodStartDate(@StartPeriod),'d',-1))
The important thing being using PeriodStartDate and suptracting 1 day, instead of PeriodEndDate and subtracting 1 month
+1Hi
Are you intending to use this for dates that aren’t start of month/end of month ever?
There’s an interesting problem here, in terms of if you _aren’t_ always comparing full periods it gets tricky.
If you are comparing start full periods, I’d use (Report.GetPeriodStartDate(@startPerod),’d’,-1)
To subtract a day from the current Period start, rather than trying to take a month off the period end.
Otherwise you need to start working in some weird find the end of month formulas, which can get a bit wacky.
(I’m a tiny bit out of practice with the ARM, someone else might have the exact code you want handy).
Hello Will,
Thank you for your response. Unfortunately, I am looking for the last day of the prior month of the financial period that I am running this report for. Example, If I’m running this report for financial period 11-2024, I am wanting this formula to output Oct 31, 2024. In another example, if I’m running this report for period 7-2024, I am wanting this formula to output June 30, 2024.
Brandon McIlhargey
+1Hi
I just tested, I’m pretty sure the below code works:
=Format('{0:MM/dd/yyyy}',DateAdd(Report.GetPeriodStartDate(@StartPeriod),'d',-1))
The important thing being using PeriodStartDate and suptracting 1 day, instead of PeriodEndDate and subtracting 1 month
Hi
I just tested, I’m pretty sure the below code works:
=Format('{0:MM/dd/yyyy}',DateAdd(Report.GetPeriodStartDate(@StartPeriod),'d',-1))
The important thing being using PeriodStartDate and suptracting 1 day, instead of PeriodEndDate and subtracting 1 month
Hill Will H,
Thank you so much for providing a solution to my problem. This is working perfectly on my instance of Acumatica and appreciate your efforts in testing!!
Regards,
Brandon McIlhargey
+1No problem, the first time I had to write a “find end of month” case statement in SQL it took me most of a day.
Enter your E-mail address. We'll send you an e-mail with instructions to reset your password.
