Community Summit Europe | March 9-12, 2020 | Barcelona, Spain Register today
View My Drafts
Well done on your efforts. Right away I see the issue you are having. You are attempting to use the account codes as the basis for your calculations. This is not best practice and will lead to a lot of errors. In Power BI you must use tables and relationships to accomplish what you want. It is much better to build out a chart of accounts with columns that mimic the exactly structure of the reports you want to replicate. You can then simply drag these columns into the Pivot Table in Excel or the Table or Matrix visual in Power BI to visualize the report you have below.
First you must build an efficient data model. A Data Model wires up the various table of data you will need to run your reports seamlessly. A good data model also simplifies the DAX formulas you will need to run your reports.
You will need at a the following tables and columns at the minimum. Do you have them?
The categories are the names of the various account heads depending on how granular you want the report and the sort orders are numbers depicting how you want to show the accounts in your report. For example Category one can have just two item (Profit & Loss Account, Balance Sheet) and category 2 can have (Revenue, Expenses, Assets, Liabilities) and you keep going down in granularity depending on how you want your reports structured.
All accounting packages come with a chart of accounts, all you need to include is the sort order columns which contain serial numbers (1,2,3,4 etc) to define the order in which you want to see your line items in your report. If for instance you want to see Revenue before Expense, then revenue will have a sort order number of 1 and Expenses a sort order number of 2.
Once you have the three tables above. You can create simple relationships between them as follows:
Simple summation of the Amount column of your GL Code table.
Amount = SUM(GL[Amount])
Time Intelligence Measure
These are the set of formulas that will give you what you require. Note, for them to work effectively in Power BI, you must define the table as the default Date table for your model. You do this by selecting the Date table from your Data View, then go to the Modeling Tab in the Ribbon and click on the button to the right called "Mark as Date Table", it will prompt you to select the column containing date in the date table, do so. This will open up the power of time intelligence in your reporting.
Below is the structure of the formulas you will need.
MTD = TOTALMTD([Amount],'Date'[Date])
MTDLY = TOTALMTD([Amount], SAMEPERIODLASTYEAR('Date'[Date]))
YTD = TOTALYTD([Amount],'Date'[Date])
YTDLY = TOTALYTD([Amount], SAMEPERIODLASTYEAR('Date'[Date]))
POP MTD = [MTD] – [MTDLY]
POP YTD = [YTD] – [YTDLY]
With all of the above done, you reports is a simple matter of:
How to Build a simple Date Table with DAX
To Build a Date Table using DAX in Power BI, follow these steps:
VAR MinYear = YEAR ( MIN ( GL[Date]) )
VAR MaxYear = YEAR ( MAX ( GL[Date] ) )
AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )
"Year", YEAR ( [Date] ),
"Month Name Long", FORMAT ( [Date], "mmmm" ),
"Mth Name Short", FORMAT ( [Date], "mmm" ),
"Month Number", MONTH ( [Date] ),
"Weekday Long", FORMAT ( [Date], "dddd" ),
"Weekday Short", FORMAT ( [Date], "ddd" ),
"Weekday number", WEEKDAY( [Date] ),
"Quarter", "Q" & TRUNC ( ( MONTH ( [Date] ) - 1 ) / 3 ) + 1)
Let us know if this helps.