Current Month and YTD columns on Income Statement

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.

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

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.

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.

https://www.dropbox.com/transfer/AAAAABa-g_M9f56fyy6Ki2zegsIfNIdN60TYg_h1xwpVpuKiLi5KMfg

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

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

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

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!

@JWilliams, a response to 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 checkbox. Thanks!