Opening and closing balance quarter in fiscal period that other than Dec

DAX DAX Calculations
1

Hi all,

I would like to get some advise for the opening and closing.

I have scenario that I develop income statement for monthly, quarterly and yearly. The opening and closing inventories work perfectly when using OPENINGBALANCEMONTH and CLOSINGBALANCEMONTH.

However, when goes to quarterly, it could not work well as it is depending on fiscal period.

For example, my fiscal period from 1 Jun 2019 to 31 May 2020.

  1. My first quarter would be from 1 Jun 2019 to 31 Aug 2019, 2nd quarter would be 1 Sep 2019 to 30 Nov 2020, 3rd quarter would be 1 Dec 2019 to 29 Feb 2020 and last quarter would be 1 Mar 2020 to 31 May 2020.
  2. Inventory balance list
    |Date|Closing Balance|
    |30-06-19| 125,600.00 |
    |31-07-19| 183,000.00 |
    |31-08-19| 110,430.00 |
    |30-09-19| 288,930.00 |
    |31-10-19| 77,834.00 |
    |30-11-19| 109,730.00 |
    |31-12-19| 380,480.00 |
    |31-01-20| 123,678.00 |
    |29-02-20| 118,450.00 |
    |31-03-20| 328,990.00 |
    |30-04-20| 327,930.00 |
    |31-05-20| 334,660.00|
  3. For Q2 which is from 1 Sep 2019 to 30 Nov 2019
    Opening balance = 110,430
    Closing balance = 77,834

Any idea how to incorporate the opening and closing balance of inventories by fiscal year using OPENINGBALANCEQUARTER and CLOSINGBALANCEQUARTER.

Thank you.

Regards,
Dennis

2

Hi @dennistgc,

@sam.mckay discuss comprehensively on the way to handle this much better with a lot of easy step to get you started in Financial Reporting With Power BI.

DAVID

3

Hi David,

Thanks for your advise. Basically i had run through the course and it is workable in normal cases like if you have fiscal year which is same with the calendar year.

However, there are also cases different from calendar year. The fiscal year would be period ended on Jan, Feb, Mar, etc… Basically I do hope someone can understand my question. I would like to apologise in advance if my English is poor and make no one understand. Do let me know which part not understand so I can try to elaborate it further.

Basically there are 3 periods can be analyzed in financial statements (Statement of Comprehensive Income, Statement of Financial Position and Statement of Cash Flow) which were by monthly, quarterly and yearly. There are definitely impressive if follow Sam’s training. However, there are also quarterly and yearly analysis which I wish to do that. In Cost of Sales = Opening Inventories + Purchase - Closing Inventories, this is the accounting formula using in our country, I am not sure whether this is the same formula apply for other countries. The reason I ask is because I wish to expand the analysis to quarterly by fiscal year.

image
The above is my income statement template.

Thank you.

Regards,
Dennis Tiong

4

@dennistgc,

You need set up your date table to reflect your first month of your fiscal year starting with June,

There is m coding that will be able to do that for you but right now i can’t find that information.

@Melissa…can you supply your coding for that?

The information that @dennistgc will be able to help setting it up that with the proper reporting levels.

Also, it would help if you supply your file pbix (power bi file) to us so we can help further.

I know what you at talking about here as your company has a different fiscal year then the calendar year.

I hope this helps.
Keith

5

Hi Keith,

Thanks for your reply.

Basically I had setting up the date table accordingly.

image
image1177×378 10.8 KB

Thank you.

6

Perfect. you should be able to use the formulas correctly :slight_smile:

7

Hi @dennistgc, 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. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!