Forum Discussion

SebSchoon1's avatar
SebSchoon1
Post Patron
3 years ago

Repaeat Value on Running total

Hello guys,

 

I use this formula
1-RT STOCK OK= 
VAR _selecteddate = SELECTEDVALUE('4- Calendrier période n'[Date])

VAR _datemax = [Last date]
RETURN
    IF(_selecteddate> _datemax,BLANK(),[Cumulative sum])

 

Here the result i get 

May be it is not clear but the Stock is well calculated day by days according to positive or negative movements.

 

Two questions,

 

Can I optimize this formula? Table is really huge and it takes ages to be calculated and even on webservices it does not work du to consumption.

 

Second question, is there a Way to have all the data repeated (which are not 0)? like this below?

 

 

Many thanks for any provided advice

 

 

 

 

Need Help

2 Replies

    • SebSchoon1's avatar
      SebSchoon1
      Post Patron
      3 years ago

      Hello amitchandak 

       

      Thanks for your quick reply,

       

      i have changed to the calendar table, but it does not work as expected.

       

      In fact i'd like to get the stock value repeated on each row up to any stock movement.

       

      Here i Get this when using date field of stock image

      which is not what i'm looking for, this is not running total.

       

       

      Here is when i use this formula.
      1-RT STOCK pas mal 2 = 

      VAR _selecteddate = SELECTEDVALUE('4- Calendrier période n'[Date])

      VAR _datemax = MAX('Max image de stock'[MAx date piece])

      VAR StockHors0 = CALCULATE(IF(ISBLANK([Cumulative sum]),BLANK(),[Cumulative sum]),'4- Calendrier période n'[Date]<='4- Calendrier période n'[Date])

      RETURN

      IF(_selecteddate> _datemax,BLANK(),StockHors0)

       

      Which is already better.

       

      but i'd like the value to be repeated until there's a new stok move.

       

      like This

       

      I get this result when I use the Date from the stock image table.

       

      When i use Te calendar Table it uses too much power

       

      Or it only shows the final value repeated on each Date, which is not correct

       

      Im really confused with the Window value since it provide me the same results

       

      Thanks for any help !