Forum Discussion

Tatmel's avatar
Tatmel
Frequent Visitor
6 years ago

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

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! 😉

 

 

Need Help

4 Replies

  • v-eachen-msft's avatar
    v-eachen-msft
    Community Support
    6 years ago

    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's avatar
      Tatmel
      Frequent Visitor
      6 years ago

      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's avatar
        v-eachen-msft
        Community Support
        6 years ago

        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 )