Balance sheet total. How to improve performance. Current measure works very slow

[Tatmel]

Hello,

I need some help with following issue. I have GL entry table with GL No, Amount, Posting Date and Index.

I also have calendar table where I have unique dates.

Based on two tables I created measure which helps me to represent balance sheet data per periods:

:Balance = calculate(sum(QGLEntry[Amount]);DATESBETWEEN('calendar'[Calendar Days];DATE(1950;01;01);ENDOFMONTH('calendar'[Calendar Days])))

When I am building matrix for the Balance sheet data with Rows structure (Category-Subcategory-GL No) and drill down to GL level, PBI run out of memory while calculating balance sheet data.

Can you help me with improving performance? I've tried to create a column with running total in GL entry table but faced with "not enough memory issue.

I have 16gb (RAM) on my pc.

Awaiting your bright solutions! 😉

[v-eachen-msft]

Hi @Tatmel ,

You could use VAR to define your filter at first.

And you could check if your Power BI is 64-bit version.

Make sure that no other software that consumes too much memory runs at the same time while running Power BI.

[Tatmel]

Hi @v-eachen-msft ,

I have 64bit version of PBI installed on my PC.

Can you please show how new measure will look like by using VAR?

Current measure looks like this:

Balance = calculate(sum(QGLEntry[Amount]);DATESBETWEEN('calendar'[Calendar Days];DATE(1950;01;01);ENDOFMONTH('calendar'[Calendar Days])))

Thank you in advance!

[v-eachen-msft]

Hi @Tatmel ,

You could refer to the following measure.

Balance =
VAR a =
    SUM ( QGLEntry[Amount] )
VAR b =
    DATESBETWEEN (
        'calendar'[Calendar Days];
        DATE ( 1950; 01; 01 );
        ENDOFMONTH ( 'calendar'[Calendar Days] )
    )
RETURN
    CALCULATE ( a; b )

[Tatmel]

Hi @v-eachen-msft ,

I've tested your measure and noticed that it gave different result compare with initial measure I've used in my calculations.

When I added to my pbix I got negative balance for fixed assets which couldnt be possible.