Roll over

[Santanak]

Hi All,

Need help in below calculation as i am new to PBI.

C= B - 15000

D = d[n-1] + c[n]

  A              B               C                   D

month	Demand	Demand - 15000	Carry forward
Jan	27342	12342	12342
Feb	7025	-7975	4367
Mar	30584	15584	19951
Apr	22044	7044	26995
June	11711	-3289	23706
July	26103	11103	34809
Aug	15266	266	35075

[MFelix · Accepted solution]

Hi @Santanak 

I assume that your month column is based on a date column add the following two measures:

v

Demand -15000 = SUM('Table'[Demand]) -15000

Carry forward =
SUMX (
    CALCULATETABLE (
        SUMMARIZE ( 'Table', 'Table'[month], "@Demand-15000", [Demand -15000] ),
        'Table'[month] <= MAX ( 'Table'[month] )
    ),
    [@Demand-15000]
)

[Santanak]

Thanks Felix for the solution. Just one more criteria, if carried forward is negative in previous row then it will not add.

[MFelix]

Hi @Santanak,

You want that if for example you have

Demand -15000	Carried Forward	Carried forward Expected
1000	1000	1000
-1500	-500	1000
500	0	1500

Is this what you mean?

[Santanak]

if CF is -ve, then in next row 0 will be added .

month	Demand	Demand - 15000	Carry forward
Jan	27342	12342	12342
Feb	7025	-7975	4367
Mar	30584	15584	19951
Apr	2204	-12796	7155
June	1171	-13829	-6674
July	2610	-12390	0
Aug	15266	266	266