Create two calculations for the same column in a matrix

[noahsdonaldson]

I don't know the best way to describe what I'm asking for but here it goes.

I have some data that has Account Numbers, Dates, Amounts, and a few attributes that classify the data like categories and types.

I've built a matrix in Power BI that shows the Categories, sub-categories, Category Detail and then SUM's the accounts based on the groups they fall into by Month/Year.  This is basically a balance sheet.

I have everything working the way it should except one piece.  

The retained earnings is calculated in the business application with a set date (Current Year -1).  It then goes and sums up the retained earnings based on a few different pieces of data in the database.  Here is the basic logic that I built in SQL and works great for a specific time period.

When the period is less than 201901 and the Type is less than 4

OR

When the Type is greater than 4 and the period is greater than or equal to 201900

AND

The period is less than the current period (IE 202005)

AND the ATTRIBUTE2 is N

AND the Account is equal to 3500

Then give me the SUM of the Amount.

If you wanted to see the retained earnings for 2018 you would simply change the dates.

My issue: Everything else in Power BI is dynamic so the user can go back to 2018 or 2000 or 1993 and see the balance sheet as it was at that time with the exception of this one calculation because its not dynamic.  I know you can't pass parameters into Power BI from SQL so I cannot just build a view or sproc that passes in params.

The last catch to all of this is that you have to show a total at the bottom of the Matrix and one of the categories is retained earnings.

Basically in my mind I have to build a DAX expression that does the calculation I have unless it's account 3500 (retained earnings) then it would need to do this other calculation, and show it all inside of one Matrix and also show the proper total at the bottom of the page.

I cannot for the life of me figure out how I would accomplish this in DAX, any help would be greatly appreciated. 

[v-qiuyu-msft]

Hi @noahsdonaldson, 

Would you please post some sample data and clarify your desired results? 

Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

Best Regards,
Qiuyun Yu

[noahsdonaldson]

Qiuyun,

Sorry for that, I have a sample PBIX but I don't see where I can attach it or an Excel file with Sample Data.

Here are some screenshots

The below-highlighted row needs a different calculation.  Everything else in the Matrix calculates correctly

with the following DAX expression

CumulativeTotal = 
    CALCULATE(
        SUM(Accounts[Amount]),
        FILTER(All(Accounts[MDY]),
        Accounts[MDY] <= MAX (Accounts[MDY])
        )
    )

The entry for Retrained Earnings uses many accounts and attributes from a couple of tables.  Here is what the sample table relationship looks like

What I can do in a SQL VIEW is

When the Accounts.Period is less than 201901 and the Type.Type is less than 4

OR

When the Type.Type is greater than 4 and the Accounts.Period is greater than or equal to 201900

AND

The Account.Period is less than the current period (IE 202005)

AND the Account.Atribute1 is N

AND the Account.Account is equal to 3500

Then give me the SUM of the Account.Amount.

I then merge this into the Accounts table and get the proper Retained Earnings.

What I'd like to do instead is make this dynamic.  Everything else in the Matrix is dynamic and I can use the Account.MDY to select different date ranges.  This of course breaks the SQL View I build because its only looking at the last year pluse the current year.  

My question is how do I build what I put in a SQL View in Power BI and make it dynamic? 

Here are my tables