Dynamic Aggregations based on positive/negative signs

[lzi]

Hi there,

I'm currently struggling with the following issue:

I have a list with account numbers and a fact table that contains the postings on the several account numbers, differentiated by different dimensions.

The total amount per account number is the running total of all months aggregated until the selected month, that I'm calculating with the following DAX measure:

 CALCULATE(SUM('FACT TABLE'[Amount (LCY)]),

FILTER ( ALL ( 'FACT TABLE'[Month]), [Month] <= MAX ( 'FACT TABLE'[Month]) )))

I firstly have to calculate the total running amount per month by account number in order to identify the sign of the value of the account in the selected month. If the sign aggregated at account level is positive, the amount should be considered as payables, if it's negative, it should be considered as receivables.

After identifying whether the amount belongs to payables or receivables in the selected month, I have to calculate the value by dimension.

I have created a calculated table that does the aggreagtion at month and account number level, which returns a table like the following:

The table is connected to the calendar table via month field. My problem is now, that as soon as I'm selecting januars 2024, the calendar table filters my aggregated table by month and does not return the account no in the example, as there aren't any postings in january 2024. But as I always need to calculate the running total until the selected date, I also need to show accounts, that do not have postings in the selected month, but in the past, as the total amount filtered by date <= selected date is greater than 0.

How can I solve this?

Thanks in advance for your help!

[lzi]

Thanks Jayleny, this helped A LOT! 🙂

[v-jialongy-msft · Accepted solution]

Hi @lzi 

The following DAX may be helpful in solving your problem.

Running_Total_Table =
VAR AllAccounts = VALUES('FACT TABLE'[Account Number])
VAR AllMonths = VALUES('FACT TABLE'[Month])
VAR AllCombinations =
    ADDCOLUMNS(
        CROSSJOIN(AllAccounts, AllMonths),
        "Running Total",
        CALCULATE(
            SUM('FACT TABLE'[Amount (LCY)]),
            FILTER(
                ALL('FACT TABLE'),
                'FACT TABLE'[Account Number] = EARLIER('FACT TABLE'[Account Number]) &&
                'FACT TABLE'[Month] <= EARLIER('FACT TABLE'[Month])
            )
        )
    )
RETURN
    FILTER(
        AllCombinations,
        [Running Total] > 0
    )

If you still have questions, please provide detailed sample pbix file and the results you expect.So that I can help you better. Please remove any sensitive data in advance.

Best Regards,

Jayleny

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.