Aggregating Measure - Performance problem

[Red]

Hi All.

I have tested for many hours and can't figure out how to make this measure.. Its suppose to show how much we have in the warehouse at a given time and it has to be based on a ItemLedgerTable, which only stores the warehouse change of an item, example 
Monday : +10,
Tuesday : -1,
Wednesday : +8,
Thursday : +7.

This forces me to make a measure that aggregates all the rows. So that it gives me:
Monday : 10
Tuesday : 9
Wednesday : 17
Thursday : 24
and so on..

The table has 935 000 rows ( and I cant filter them away, since all rows need to be countet to have a complete view of the warehouse).

------

What I have is:

Measure =

VAR PrevDay =

CALCULATE (

LASTDATE ( 'Calendar'[Date] ),

FILTER (

ALL ( 'Calendar'[Date] ),

'Calendar'[Date] < MAX ( 'Calendar'[Date] )

)

)

VAR res =

CALCULATE(

SUM('ItemLedgerTable'[Quantity]),
'Calendar'[Date] <= PrevDay)

RETURN IF(res< 0, 0 , res)

My "solution" works fine, but its so demanding for PowerBI Services that I get an error.
This seems so simple but i just cant make it faster. Does anyone have any experience or know of some tricks? It would be greatly appreciated.

[v-zhangti]

Hi, @Red 

I simulated some data, hoping to recover your problem.

Simple measure:

Measure = 
    CALCULATE (
        SUM ( 'Table'[Quantity] ),
        FILTER ( ALL ( 'Table' ), [Date] <= MAX( ( 'Table'[Date] ) )
    )
)

Please refer to the following pbix documents.

If the method I provided above can't solve your problem, what's your expected result? Could you please provide more details for it?

Best Regards,

Community Support Team _Charlotte

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