topic Business Central Profit and Loss in PowerBI in DAX Commands and Tips

Business Central Profit and Loss in PowerBI

Anonymous — Sun, 10 May 2020 08:56:41 GMT

G'Day All

This post is hopfully going to help future learners wanting to create a Profile and Loss statement using PowerBi for Business Central specifically. I have looked though a few posts on this topic but I dont see the best way ahead. Your help is very much appreciated.

The challenge I am experiencing to ensuring the relationships are correct but also being able to return data month by month (decreasing across the page)

As of 2020 Business Central has these Accouting Tables to help get this profit and loss table.

Chart_Of_Accounts

G_LEntries

DimensionSetEntries

Date (which is the DAX Date Template shared by SQLBI.com)

Please note that there is a many to many relastionship from G_LEntries (Dimension_Set_ID) and DimensionSetEntries (Dimension_Set_ID)

Question 1. How do I return the sum of G_LEntries(Amount) Starting from the current Month and working from left to right to return each month eg April 2020, March 2020 . The Chart_Of_Accounts must only filter on Posting so that we dont calulate the beginning and end totals the way Business Central Does.

Note: I'd suggest that you Group your GL account numbers into logical groups before you commence slicing data. Eg Incoming on Sales and or Income on Interest, etc.

Question 2 How to use the 445 financial peroid method so that this data can also be divided across the page but also retuning the correct date syntax eg [1 Jul - 28 Jul 2019],[29 Jul - 25 Aug 2019],[26 Aug 2019 - 29 Sep 2019],[30 Sep 2019 - 27 Oct 2019] etc.

I will try to edit this document so that it becomes the definative resource for all.

Thank you Power BI Community

Re: Business Central Profit and Loss in PowerBI

Greg_Deckler — Sun, 10 May 2020 11:16:29 GMT

First of all, bravo for how you posted your data model. That is superb. I sincerely hope that your method of posting that catches on with people because it makes things soooooo much easier to understand. Really well done.

Sample data and expected output would have been nice but I'll give it a shot at answering your questions. So for the first question. I am going to assume that you will put some column from Chart_of_Accounts like Name into the Rows of a Matrix visualization. Then you would put maybe a Year/Month column from Date into the Columns. You could then filter this for a "type" in your GL_Entries for only postings. I am making a lot of assumptions here.

There are a bunch of threads on 445 like this one: https://community.powerbi.com/t5/Desktop/Custom-445-Fiscal-Year-Calendar/td-p/161257 I believe it really comes down to having the correct values in the correct columns in your Date table to implement the 445. It's a matter of creating custom columns in the Date table.

Re: Business Central Profit and Loss in PowerBI

Anonymous — Sun, 10 May 2020 19:57:57 GMT

This is a full solution:

https://www.thebiccountant.com/2016/07/21/easy-profit-loss-account-scheme-reports-power-bi-power-pivot-dax/

Best
D

Re: Business Central Profit and Loss in PowerBI

Anonymous — Sun, 10 May 2020 20:02:08 GMT

This also shows you some technique:

https://higherlogicdownload.s3-external-1.amazonaws.com/PBIUSERGROUP/Financial%20Models%20%26%20Calculations%20Presentation.pdf?AWSAccessKeyId=AKIAVRDO7IEREB57R7MT&Expires=1589144097&Signature=SQZ9XYqs33sqT%2F8PJTLQ0hicqCc%3D

Best
D

Re: Business Central Profit and Loss in PowerBI

Anonymous — Sun, 10 May 2020 23:06:18 GMT

Thanks for the posting. That URL returns this error access denied. Am I doing something wrong?

Re: Business Central Profit and Loss in PowerBI

Anonymous — Mon, 25 May 2020 01:26:21 GMT

HI Greg

I think I have worked out the filter that i need

I am not sure if I can modify the original post but let me try and verbalise the issue.

Dimension Set ID table looks like this

Dimension_Set_ID	Dimension_Code	Dimension_Value_Code	Dimension_Value_ID	Dimension_Name	Dimension_Value_Name
1	Entity	100	3	Entity	Entity #1
2	Entity	101	6	Entity	Entity #2
3	Cost Centre	8000	3	Cost Centre	Store Name 8000
4	Cost Centre	8001	5	Cost Centre	Store Name 8001
5	Entity	100	4	Entity	Entity #1
6	Cost Centre	8002	6	Cost Centre	Store Name 8002

So when I want to filter on Dimension_Value_Code = 100 I also want the related Cost Centre to be filtered

Entity = 100 (includes Cost Centre 8000 and 8001)

Entiy = 101 (includes Cost Centre 8002)

Here is the link to the detailed overview Microsoft Business Central https://docs.microsoft.com/en-us/dynamics365/business-central/design-details-dimension-set-entries-overview

Thank you