Opening and closing inventories

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 , we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.
  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.
  • Use the proper category that best describes your topic
  • Provide as much context to a question as possible.
  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.

3

Hi, may I know what is the problem of my posting? So I can avoid in the future.

Thank you.

4

Hi @dennistgc. Its easier for us to identify the issue if you include a PBIX file with your work-to-date, a sample dataset (e.g., in Excel), and a mock-up of your expected results (e.g., in Excel). Thank you.

5

Thanks for posting your question @dennistgc. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question