Current Month and YTD columns on Income Statement

Power BI
1

I’m trying to create an income statement with the following value columns:
Monthly Activity (this year)
Monthly Activity (last year)
YTD (this year)
YTD (last year)

I have separate filters for year and month. I followed the approach and procedures described in Enterprise DNA’s video called Financial Reporting w/Power BI but I can’t seem to get the solution to calculate MTD and YTD correctly using the page filters. When I choose a month, I only get monthly activity. I use TOTALYTD function on other reports with success but I haven’t been able to get it working in this solution. I’m hoping another user has successfully implemented Sam’s solution with the results I’m looking for. There are many layers to Sam’s approach. I suspect to get date intelligence to work correctly, I need to apply a date filter or TOTALYTD calculation in this calculated table.

P&L Data =
CALCULATETABLE(
SUMMARIZE(Transactions, Transactions[Company], Dates[MonthInCalendar],
AccountMap[LineItem], AccountMap[Class], AccountMap[SubCategory],
AccountMap[Sort], “First Date”, MIN(Dates[Date]),
“Amount”, [Account Balance] ),
AccountMap[Report] = “PL”

Thanks.

2

Hi, JWiliams. Can you share the file to we could find a mistake?

3

I would love to. Thanks. I’m using Direct Query so I probably need to export some data to Excel to be useful to you. Unless you have other advice to share my pbix file with you. Thanks.

4

You can access the PBIX example using the link below. Ideally, I need to add MTD and YTD columns for current year and prior year. I’ve also tried to add drill through, but it seems the amounts presented are too extracted from my transactions table to make it work. Thanks again any advice you may have.

5

Wondering if the example file I provided is working for you. Let me know if I need to make any adjustments and resend.

6

Yes, everything is fine. As soon as I get some time I will look. Sorry I got too much work to do.

7

Hi @JWilliams , you seem to have a lot of fact tables? I think the table marked “transaction table” has all fact data …Debit, Credit and Amount (negative or positive) for both balance sheet and profit and loss is that right?
Cheers
Garry

8

For the example project I sent, you are correct, Transactions is the only fact table. I created a copy of a more comprehensive project and trimmed it down for this example. Still cluttered. Thanks for your help!

10

Garry, I see you marked your reply as “Solution”. Can you help me locate the solution? Did you transfer a working file example or a written post? I can’t seem to find anything. Thanks!

11

@JWilliams sorry I didn’t mark it as solution Enterprise DNA did.

I haven’t had a chance to look at your query sorry been busy on work stuff!

12

Hi @JWilliams, sorry, this was an oversight. The Solution tag has been removed. I understand that you still need further assistance on this. Kindly continue the thread by using the comment box below. Thanks!

13

@EnterpriseDNA @JWilliams

So, I am just thinking whether this model will be too slow given the measures required and filtering of time periods.

This financial reporting method works but the process is very CPU intensive.

Has anyone on the forum tried this approach with the given column metrics for This Year v Last Year by Month and also year to date position?

14

I’m unable to download the sample file anymore. But I would have thought that these measures aren’t too computationally intensive if the data is setup in a logically way.

But I can’t actually see anything with the data at the moment unfortunately.

Sam

15

Hi @JWilliams, we’ve noticed that no response has been received from you since December 13, 2019. We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved. You may reopen a new thread when the need arises. Thanks!