How to calculate Historial total for Previous Years

[BIAB]

Hello everyone

In finance the previous Net Income historical amount up to Dec 31 becomes part of Retained Earning in the current year.  I need to be able to calculate the accumulative total Net Income up to Dec 31 and use that amount in the current year. 

If for example I use the following dax expression I get what I need for the Balance Sheet in 2019

CALCULATE (
            [Total Gross Profit] - [Total Operating Expense] - [Total Taxes/Depreciation/Amortization],
            'Date'[Year] < 2019         
) 

But I need to replace 2019 with an expression so that If for example I look at 2018 Balance Sheet I see the total accumulative Net Income for anything <2018.  I try with using EARLIER but that didnt work.  Any idea how I can accomplish this?

Thanks in advance for any input you can provide

[TeigeGao]

Hi BIAB,

According to your description, my understanding is that you want to calculate the Historial total based on year and can change them without changing the DAX query.

In this scenario, we can use the following measure:

Measure = CALCULATE (

            [Total Gross Profit] - [Total Operating Expense] - [Total Taxes/Depreciation/Amortization],

            FILTER(ALL('Date'),'Date'[Year]< SELECTEDVALUE('Date'[Year]))     

)

Then we can create a slicer which contains all years, then you can select one year from them, this measure will return different result based on the year you selected.

Best Regards,

Teige

[BIAB · Accepted solution]

I was able to get what I needed with a filter that goes as follow:

VAR __totalParentID990 =
                    CALCULATE (
                        SUMX (
                            FILTER ( AccountLedgerUnion, AccountLedgerUnion[Subsidiary Parent ID] = "990" ),
                            AccountLedgerUnion[Amount2]
                        ),
                        FILTER (
                            ALLSELECTED ( 'Date'[Date] ),
                            'Date'[Date]
                                <= DATEVALUE ( "12/31/" & YEAR ( MAX ( 'Date'[Date] ) ) - 1 )
                        )
                    )

AccountLedger-7-TotalPYNetIncomeForBalanceSheetRetainedEarnings = 
SUMMARIZE ( TRANSACTION_LINES, 
    'Date'[Year], 
    'Date'[MonthNo], 'Date'[YearMonthNo], ACCOUNTING_PERIODS[ACCOUNTING_PERIOD_ID],
    SUBSIDIARIES[NAME],
    SUBSIDIARIES[SUBSIDIARY_ID],
    SUBSIDIARIES[Location],
    "Sequence Type", 90.00,
    "Sequence Category", BLANK (),
    "Sequence Subcategory", "94.05",
    "Type", "Equity",
    "Category", BLANK (),
    "Sub Category", "Retained Earnings (**bleep**. Profit)",
    "Entity", "",
    "Entity Type", "",
    "EE Name", "",
    "Industry", "",
    "MD 1", "",
    "MD 2", "",
    "MD 3", "",
    "MD 4", "",
    "Account", "",
    "Account ID", 0,
    "URL", "",
    "Transaction Date", LOOKUPVALUE (
        ACCOUNTING_PERIODS[ENDING],
        ACCOUNTING_PERIODS[ACCOUNTING_PERIOD_ID], ACCOUNTING_PERIODS[ACCOUNTING_PERIOD_ID]
    ),
    "Transaction ID", "",
    "Transaction Type", "",
    "Reference", "",
    "Status", "",
    "Amount", BLANK (),
    "Amount2", 
    0
        + 
            CALCULATE ( 
            [Total Gross Profit] - [Total Operating Expense] - [Total Taxes/Depreciation/Amortization],

            FILTER (
                'Date',
                'Date'[Year] <= MAX('Date'[Year])
            )
        )
        
        * -1,
    "Subsidiary Parent ID", "990",
    "Payment Method ID", "",
    "FX Key", ""
)

[BIAB]

Hi @TeigeGao 

The report should not be limited to a year selection but should show the total Retained Earning correctly to its corresponding years.  I can be looking at data from 11/2018 to 2/2019. 

The data for Retained Earnings needs to be the historical cummulative total  of Net Income up to dec 31 of the prior year.  For example, using the data from the screen-shot I previously submitted,  the Retained Earing  will show $100 in 2018 and $400 in 2019. 

[BIAB]

Any advice is
 appreciated.