web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Finance | Project Operations, Human Resources, ...
Answered

Creating a Quarterly Column in Financial Reporting

(2) ShareShare
ReportReport
Posted on by 208
How would be the suggested way to create a column in financial reporting that allows you to create a column that is looking at the current quarter the report run date is in?  If on a calendar year Jan-Dec for example, and I run the report in February, the report should report only on Jan-Feb data.  If the report is run in October, it would only pull that data because nothing is there for November or December.  This would be for actual totals.
Thank you
Categories:
I have the same question (0)
  • Suggested answer
    Ramesh Kumar Profile Picture
    7,511 Super User 2025 Season 2 on at
    Creating a Quarterly Column in Financial Reporting
    Use Period Calculation Filter: Set Period to BASE-((MOD(MONTH(@CURDATE)-1)/3)*3):BASE
     
     
     
    If this was helpful, please check the "Does this answer your question?" box and mark it as verified.
  • CU03101700-0 Profile Picture
    208 on at
    Creating a Quarterly Column in Financial Reporting
    Hi Ramesh,
    Is it ok that it auto changes your formula to 
    BASEROW-((MOD(MONTH(@CURDATE)-1)/3)*3):BASEROW
     
    I changed the column type to CALC and then put your information in under Formula and the above is what it changed it to afterwards.
    Or, are you saying to do the following:
    Column Type - CALC
    Period - BASE-((MOD(MONTH(@CURDATE)-1)/3)*3
  • CU03101700-0 Profile Picture
    208 on at
    Creating a Quarterly Column in Financial Reporting
    Yes I am not having any luck getting a formula to work properly where it could know the quarter you are in and calculate it accurately.  
  • Verified answer
    CU03101700-0 Profile Picture
    208 on at
    Creating a Quarterly Column in Financial Reporting
    Here was what I found to be the recommended fix.  Depending on your preference you will have two ways of creating this.
    Method one
    Create four different column definitions - one for each quarter (1-4) and a fifth column for calculation
    In a new column (#1 for example) you will have:
    Column type - FD
    Book Code - 'your choice as my was budget info'
    Fiscal Year - BASE
    Period 1
    Periods Covered - Periodic
    Print Control - NP,P<=B 
    The 'B' column referenced above was a YTD total for something else
     
    In a new column (#2 for example and so on)
    Same attributes
     
    Now that you have all four quarters, the fifth column would be the total so I set it up as follows:
    Column Type - CALC
    Formula - C+D+E+F (this represents the column buckets capturing each of the QTD amounts for each period
     
    So in this method, you can reuse the column definition and change your 'Period' attribute to 4,5,6 and then 7,8,9 and lastly 10,11,12 and only have the one column definition.
     
    Method two
    Copy the column definitions out calling each one QTD 1, QTD 2, QTD 3, and QTD 4 with each column definition having the right periods in each.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Leaderboard > Finance | Project Operations, Human Resources, AX, GP, SL

#1
CA Neeraj Kumar Profile Picture

CA Neeraj Kumar 2,157

#2
André Arnaud de Calavon Profile Picture

André Arnaud de Cal... 883 Super User 2025 Season 2

#3
Sohaib Cheema Profile Picture

Sohaib Cheema 674 User Group Leader

Last 30 days Overall leaderboard

Product updates

Dynamics 365 release plans