topic Cumulative Total with dynamic and effective filter in DAX Commands and Tips

Cumulative Total with dynamic and effective filter

Talal141218 — Wed, 31 Jan 2024 11:06:00 GMT

Hi Profis,

I have an issue here and wana your help.

I create Total cumulative measure as below :

CALCULATE([" Ford."], FILTER(ALLSELECTED(Dim_LedgerAccount[LedgerAccount]), Dim_LedgerAccount[LedgerAccount]<= MAX(Dim_LedgerAccount[LedgerAccount])) , FILTER(ALLSELECTED(Dim_Date), Dim_Date[Date_NK] <= MAX(Dim_Date[Date_NK]))).

Everything läuft smoothy, till i select a month from filter. Then the measure will been brocken as in the screen shot :

Thanks in advance for your help.

Re: Cumulative Total with dynamic and effective filter

123abc — Wed, 31 Jan 2024 11:14:25 GMT

It seems like you're trying to create a cumulative total measure, but you're encountering issues when selecting a specific month from the filter. The problem might lie in how your measure is interacting with the filter context.

Here's a revised version of your measure that might work better:

Total Cumulative =
CALCULATE(
[YourMeasure],
FILTER(
ALLSELECTED(Dim_LedgerAccount[LedgerAccount]),
Dim_LedgerAccount[LedgerAccount] <= MAX(Dim_LedgerAccount[LedgerAccount])
),
FILTER(
ALLSELECTED(Dim_Date),
Dim_Date[Date_NK] <= MAX(Dim_Date[Date_NK])
)
)

Replace [YourMeasure] with the measure you want to cumulate. This formula aims to calculate the cumulative total based on the maximum values of the Ledger Account and Date in the current filter context.

However, if selecting a specific month is breaking the cumulative total, it might be due to the way your date filtering is set up or how your data model is structured.

Ensure that your date table is correctly related to your fact table, and check if the relationships are set up to handle filtering appropriately. Additionally, verify that the date column you're using in your filter has the correct granularity for your analysis.

If the issue persists, providing more details about your data model and the specific behavior you're observing could help in providing a more tailored solution.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

Re: Cumulative Total with dynamic and effective filter

Talal141218 — Wed, 31 Jan 2024 11:23:19 GMT

Hi,

Thanks for your quickly response. I will share with you the measure i created to calculate the Accounts receivable :

" Ford." = CALCULATE(Fact_GeneralLedger[CM_Fact_GeneralLedger_Amount_CCY], FILTER(Dim_LedgerAccount,Dim_LedgerAccount[LedgerAccount] = "1200")).

I will be thankful for your replay.

Re: Cumulative Total with dynamic and effective filter

123abc — Wed, 31 Jan 2024 14:03:24 GMT

It seems like you want to calculate the accounts receivable specifically for the "Ford" ledger account (assuming "1200" represents the ledger account for Ford). Your measure looks correct for calculating the accounts receivable for that specific ledger account.

The measure you provided calculates the total amount from the Fact_GeneralLedger table where the LedgerAccount is "1200". Here's the measure you provided:

" Ford." = CALCULATE(Fact_GeneralLedger[CM_Fact_GeneralLedger_Amount_CCY], FILTER(Dim_LedgerAccount, Dim_LedgerAccount[LedgerAccount] = "1200"))

This measure filters the Fact_GeneralLedger table to only include rows where the LedgerAccount is "1200", and then it sums up the values in the CM_Fact_GeneralLedger_Amount_CCY column for those rows.

If you're looking to create a cumulative total for the accounts receivable for Ford, you can use a measure similar to the one provided in your original question, but tailored for the "Ford" ledger account specifically:

Cumulative_Ford_AR =
CALCULATE(
[Ford.],
FILTER(
ALLSELECTED(Dim_LedgerAccount),
Dim_LedgerAccount[LedgerAccount] = "1200"
),
FILTER(
ALLSELECTED(Dim_Date),
Dim_Date[Date_NK] <= MAX(Dim_Date[Date_NK])
)
)

This measure will calculate the cumulative accounts receivable for the "Ford" ledger account over time, considering all dates and ledger accounts if no specific filters are applied.

Remember to adjust the measure according to your data model and requirements. If you have any further questions or need additional assistance, feel free to ask!

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.