Financial Reporting Previous Year Actuals

DAX DAX Calculations
1

Hello,
I’m creating financials for a small business using the methods in the Financial Reporting course and have run into an issue. The Same Year Actuals measure works well but when I apply any date filter (year or FY), the Previous Year Actual measure only shows the summary total amounts and NOT the itemised actuals. I’ve been all over this an cannot work out what I’ve done wrong. Love the material by the way!

Here’s my measure;

Previous Year Actuals = 
VAR CurrentItem =
    SELECTEDVALUE ( 'Income Statement Template'[Items Normalised] )
RETURN
    SWITCH (
        TRUE (),
        CurrentItem = "Total Revenue", [Revenue LY],
        CurrentItem = "Total Direct Costs", [Direct Costs LY],
        CurrentItem = "Total Gross Profit", [Gross Profit LY],
        CurrentItem = "Gross Profit %", FORMAT ( [Gross Profit Margin LY], "0.00%" ),
        CurrentItem = "Total Expenses", [Expenses LY],
        CurrentItem = "Total Net Profit", [Net Profit LY],
        CurrentItem = "Net Profit %", FORMAT ( [Net Profit Margin LY], "0.00%" ),
        CALCULATE (
            CALCULATE (
                [Actuals LY],
                FILTER (
                    'Account Transactions',
                    'Account Transactions'[Account Name] = CurrentItem
                )
            )
        )
    )

Income Statement Example
Income Statement Example1308×748 79.1 KB

2

Hi @mkaess, 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

Issue solved. I went back to the video and reconstructed the Previous Year Actuals measure to match the one Sam did. The issue was where the DATEADD functions was placed in the filter(s). Sometimes the most obvious problem isn’t.

4

Previous Year Actuals =
VAR CurrentItem =
SELECTEDVALUE ( ‘Income Statement Template’[Items Normalised] )
RETURN
SWITCH (
TRUE (),
CurrentItem = “Total Revenue”, [Revenue LY],
CurrentItem = “Total Direct Costs”, [Direct Costs LY],
CurrentItem = “Total Gross Profit”, [Gross Profit LY],
CurrentItem = “Gross Profit %”, FORMAT ( [Gross Profit Margin LY], “0.00%” ),
CurrentItem = “Total Expenses”, [Expenses LY],
CurrentItem = “Total Net Profit”, [Net Profit LY],
CurrentItem = “Net Profit %”, FORMAT ( [Net Profit Margin LY], “0.00%” ),
CALCULATE (
CALCULATE (
[Actuals],
FILTER (
‘Account Transactions’,
‘Account Transactions’[Account Name] = CurrentItem
)
),
DATEADD ( ‘Calendar’[Date], -1, YEAR )
)
)