Calculate consecutive backlogs over a specific threshold value.

[Anonymous]

I have a sample data table with given Columns A, B and E. Need to calculate Columns C and D as outputs in Power BI and use Col D in Histogram. Giving a Cyclic reference error as it needs to calculate using a previous value from same column. I have tried using Lookupvalue but still giving cyclic error. Thanks for your kind help -

[smpa01 · Accepted solution]

@Anonymous  you can write a measure like this

Measure =
CALCULATE (
    [dropPlusRollOver],
    OFFSET ( -1, DISTINCT ( ALL ( 'fact' ) ), ORDERBY ( 'fact'[Date], ASC ) )
)
    + MAX ( 'fact'[Drop] )

PFA

[Anonymous]

Thanks for the response but Column C and Column D have to be output using Column A and Column B and a capacity parameter. i think you used them as input for measures. 

Also i need to sho highlighted Column D in a histogram visual.

[smpa01]

@Anonymous  this is a good place to start 

[smpa01]

@Anonymous  provide a sample data that here that can be copied over to PBI. Power BI at current capacity is unable to extract data from a pic.

[Anonymous]

Date	Drop	Drop Plus Rollover	Left from Previous Day	Capacity
1/1/2022	9500			10000
1/2/2022	10112	10112	0	10000
1/3/2022	8500	8612	112	10000
1/4/2022	8600	8600	0	10000
1/5/2022	11122	11122	0	10000
1/6/2022	12000	13122	1122	10000
1/7/2022	9980	13102	3122	10000
1/8/2022	8500	11602	3102	10000
1/9/2022	7400	9002	1602	10000
1/10/2022	6233	6233	0	10000
1/11/2022	12344	12344	0	10000
1/12/2022	9999	12343	2344	10000
1/13/2022	11223	13566	2343	10000
1/14/2022	8877	12443	3566	10000
1/15/2022	8500	10943	2443	10000
1/16/2022	4500	5443	943	10000
1/17/2022	13222	13222	0	10000
1/18/2022	8500	11722	3222	10000
1/19/2022	9422	11144	1722	10000
1/20/2022	8888	10032	1144	10000
1/21/2022	7777	7809	32	10000