There's not enough memory to complete this operation

[anwargabr]

Dear Power BI Experts,

I’m working with a database that contains hundreds of loans, each identified by a unique Loan Number.

The Date field represents the date the loan exists, and I’m using CROSSJOIN DAX to calculate dates between the “From” and “To” columns, which is crucial for calculating daily interest and outstanding balances.

To manage this,I have created a calculated column called “Previous Date for Loan” using the DAX formula below. This calculted column allows me to use DATEDIFF to determine the variance between the calculated previous date and the current date. This helps identify any missing dates if the result is >1 like the highted cells in the above image so I can adjust the “From” or “To” columns to ensure all dates are accounted for.

Previous Date for Loan = 
VAR LoanREF = Fact_Loans_Details[Loan Number]
VAR Date2 = Fact_Loans_Details[Date]
return
CALCULATE(MAX(Fact_Loans_Details[Date])
,FILTER(
    all(Fact_Loans_Details)
    ,Fact_Loans_Details[Date] < Date2 && Fact_Loans_Details[Loan Number] = LoanREF
)
)

DATEDIFF = DATEDIFF(Fact_Loans_Details[Previous Date for Loan],Fact_Loans_Details[Date],DAY)

However, due to the high volume of data, I’ve started receiving the following error message in Power BI Desktop:

“There's not enough memory to complete this operation. Please try again later when there may be more memory available.”

I would appreciate your assistance in determining whether the DAX formula I’m using is optimized or if there’s a better approach to calculate the previous date for each loan to avoid memory issues.

Thanks,

[Anonymous]

Hi @anwargabr ,

Based on the description, try to end other unnecessary tasks

Besies, try filtering the data in the Power Query editor before loading.

You can also view the following links to learn more information.

Solved: Not enough memory to complete this operation - col... - Microsoft Fabric Community

Solved: Extensive memory consumption causing problems and ... - Microsoft Fabric Community

PBI Error : There's not enough memory to complete ... - Microsoft Fabric Community

Best Regards,

Wisdom Wu

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

[littlemojopuppy]

Hi.  There are a number of financial functions in DAX that can probably help simplify this for you...