Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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:
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!
Solved! Go to 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.
Thanks Jayleny, this helped A LOT! 🙂
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.