Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Talal141218
Helper III
Helper III

Cumulative Total with dynamic and effective filter

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 : 
Talal141218_0-1706698989225.png

 

Talal141218_1-1706699083026.png

Thanks in advance for your help. 

 

3 REPLIES 3
123abc
Community Champion
Community Champion

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.

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. 

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.

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.