Insuffisant memory for totalYTD Calculation

[cgan13]

Hi everybody, 

I have a problem on a calculation with the error message "insuffisant memory for finishing the operation", if you could help me?

I have a table with a bit more than 1 million lines. It represent some event with the date of the event and the duration. I have it in seconds and created 2 others columns for having the event in minutes or hours. I would want to have a view of the events with a year to date and also comparing it after with the YTD of the previous year. But I can calculte the YTD because I have this error message

My formula is this one. My referance for the date is the column EVE_DEB_DATE and I want to sum the column total_Duration_Sec : 

Duration_h_YTD = TOTALYTD(SUMX(TABLE,(TABLE[total_Duration_Sec]/3600)),TABLE[EVE_DEB_DATE])

I tried with the SUM normal function, and not the SUMX but the problem is the same. I'm not an advanced user sorry

Thanks for your help

[Sahir_Maharaj · Accepted solution]

Hello @cgan13,

The "insufficient memory" error message in Power BI usually indicates that the calculation you're trying to perform is too complex and requires too much memory to complete. In your case, it may be because you're trying to perform a YTD calculation on a large table with over a million rows.

One way to potentially solve this issue is to pre-aggregate your data at a higher level before calculating the YTD.

1. Create a new table with distinct values of the EVE_DEB_DATE column:

DateTable = DISTINCT(TABLE[EVE_DEB_DATE])

2. Create a calculated column in the DateTable that extracts the month and year from the EVE_DEB_DATE column:

MonthYear = FORMAT(DateTable[EVE_DEB_DATE], "yyyy-MM")

3. Create a new table that summarizes your data by month and year:

SummaryTable = SUMMARIZECOLUMNS(
    DateTable[MonthYear],
    "TotalDurationSec", SUM(TABLE[total_Duration_Sec])
)

4. Create a calculated column in the SummaryTable that calculates the YTD value for each month:

Duration_h_YTD = 
TOTALYTD(
    SUM(SummaryTable[TotalDurationSec])/3600,
    DateTable[EVE_DEB_DATE],
    ALL(DateTable),
    "MonthYear",
    DateTable[MonthYear]
)

Hope this helps!