DAX measure - IF with sum arguments

[GustavBA]

Hi all

I have a DAX challenge that i haven't been able to solve.

I have 3 related tables;

GL Account (columns: No, Name, type)
GL Entries (Columns: Date, GL account no, Amount)
Calender (columns: Date, year, financial year)

I wish to make a measure (only 1) Which calculates sum(GL Entries[Amount]) when the Account type in GL Account table has value "Income" and when GL Account table has value "Balance" the calculation should be like
CALCULATE(Sum(GL Entries[Amount]),FILTER(ALL(Calender[Date]),DimDate[Date] <= Max(Calender[Date])))

This would enable me to return a pivottable to excel with accounts as rows and the amount on income accounts will be filtered by a date filter interval (eg may 2018) and the amount on balance accounts will always the the total balance amount per max filter date (eg. 31st of may 2018).

Any suggestions?

Br Michael

 

[Matt Allington]

Try this
=Switch(selectedvalue(glaccount[account]),“income”,[measure 1],”balance”[measure 2],blank())

 

[macfuller]

I hope Matt's answer was sufficient for you, but I'm interested in your data as I work with GL accounts as well. Are your accounts either/or Income vs Balance? (I assume you have Expense, Inventory, and other types in there too somewhere.) So is one GL Account entry (e.g. 1248, "Deposit Account, "Income") unique? I assume so since you appear to be taking the 1:many relationship between Account and Entries for granted in your sample measure and [DimDate] is actually the GL Entries[Date] field.

At a basic level your measure seems like it should work for the cumulative Balance summation as long as you have the Account-Entries relationship and your pivot table is filtering by Account Type. (I believe the date table should NOT have a relationship if you're trying to perform the cumulative summation you've noted, even with your ALL() statement in the FILTER argument, but I can certainly be wrong there!) So in your table if you had Accounts by row and Type by column your Income, Expense, and Balance should line up.

I think you'll need a 2nd measure for the monthly Income rollup because you want just the month, not cumulative. One option is to define the Income measure explicitly at a monthly level if your reports will always be monthly. Something like FILTER('GL Entries', YEAR('GL Entries'[Date]) = YEAR(Calendar[Date]) && MONTH('GL Entries'[Date]) = MONTH(Calendar[Date])).

 

[macfuller]

On review I don't know if it's true the date table can't have a relationship. Try it both ways.

Another Income formula option could be

SUMX (
    CALCULATETABLE (
        'GL Entries',
        FILTER (
            'GL Entries',
            'GL Entries'[Date] <= MAX ( 'Calendar'[Date] )
        )
    ),
    'GL Entries'[Amount]
)

I think for the cumulative balance you could swap the FILTER argument to FILTER(ALL(Calendar[Date]), Calendar[Date] = MAX('GL Entries'[Date])). Again, try these with and without a relationship.

 

[macfuller]

Sorry, I keep hitting the 10-minute edit limit as I write my responses. You are running against actual data so you can use a connected date table. Apologies for making things more complicated than necessary.

In case you ever need to run forecasts from a single base amount, that does require a disconnected date table. For example, if you want to forecast monthly from your last actual, and don't want to generate data for every future time period, you'll need to do that as there's no actual data for the calendar table to go against. e.g. your December 2018 numbers are your latest values and you want to forecast 2019 monthly based on a .25% monthly increase. You'll expand your calendar table to 2019 first. Then you could generate each value x 12 2019 values into a table, or you can use a disconnected date table to forecast both monthly and cumulative using measures. As I forecast a multiple values I find the measure approach is much more flexible as it can respond to variable interest rates and other scenarios without having to rerun the query each time.