topic Re: Calculate consecutive backlogs over a specific threshold value. in DAX Commands and Tips

Calculate consecutive backlogs over a specific threshold value.

Anonymous — Wed, 08 Feb 2023 17:52:02 GMT

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 -

Re: Calculate consecutive backlogs over a specific threshold value.

smpa01 — Wed, 08 Feb 2023 16:20:22 GMT

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.

Re: Calculate consecutive backlogs over a specific threshold value.

Anonymous — Wed, 08 Feb 2023 16:23:13 GMT

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

Re: Calculate consecutive backlogs over a specific threshold value.

smpa01 — Wed, 08 Feb 2023 16:51:15 GMT

Anonymous you can write a measure like this

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

PFA

Re: Calculate consecutive backlogs over a specific threshold value.

Anonymous — Wed, 08 Feb 2023 17:05:40 GMT

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.

Re: Calculate consecutive backlogs over a specific threshold value.

smpa01 — Wed, 08 Feb 2023 17:06:03 GMT

Anonymous this is a good place to start