The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
Hi,
I would like to show how many consecutive days into the future we have no available capacity.
By consecutive meaning, if there is available capacity today but sold out tomorrow, the return value should be 0.
Notice that there is several production areas, for which we need the calculation.
I have tried creating a temporary column, to show if there is available capacity on that paticular day (0 / 1 value only).
For each OpsGrp I would like to count the number of consecutive days, where the temp col value > 0.
Calendar day | Plant | OpsGrp | Capacity | Bookings | Temp Col - Sold out | DaysSoldOut |
12-04-2018 | 1 | A | 275 | 331 | 1 | 2 |
12-04-2018 | 1 | B | 190 | 70 | 0 | 0 |
12-04-2018 | 1 | C | 1164 | 1055 | 0 | 0 |
12-04-2018 | 1 | D | 339 | 335 | 0 | 0 |
12-04-2018 | 1 | E | 310 | 258 | 0 | 0 |
12-04-2018 | 1 | F | 507 | 434 | 0 | 0 |
13-04-2018 | 1 | A | 275 | 630 | 1 | 1 |
13-04-2018 | 1 | B | 190 | 13 | 0 | 0 |
13-04-2018 | 1 | C | 1164 | 1573 | 1 | 3 |
13-04-2018 | 1 | D | 339 | 341 | 1 | 1 |
13-04-2018 | 1 | E | 310 | 388 | 1 | 1 |
13-04-2018 | 1 | F | 507 | 802 | 1 | 1 |
16-04-2018 | 1 | A | 275 | 133 | 0 | |
16-04-2018 | 1 | B | 190 | 64 | 0 | |
16-04-2018 | 1 | C | 1164 | 1257 | 1 | |
16-04-2018 | 1 | D | 339 | 330 | 0 | |
16-04-2018 | 1 | E | 310 | 308 | 0 | |
16-04-2018 | 1 | F | 507 | 475 | 0 | |
17-04-2018 | 1 | A | 275 | 311 | 1 | |
17-04-2018 | 1 | B | 190 | 39 | 0 | |
17-04-2018 | 1 | C | 1164 | 1250 | 1 | |
17-04-2018 | 1 | D | 339 | 330 | 0 | |
17-04-2018 | 1 | E | 310 | 308 | 0 | |
17-04-2018 | 1 | F | 507 | 475 | 0 | |
18-04-2018 | 1 | A | 275 | 277 | 1 | |
18-04-2018 | 1 | B | 190 | 103 | 0 | |
18-04-2018 | 1 | C | 1164 | 1043 | 0 | |
18-04-2018 | 1 | D | 339 | 330 | 0 | |
18-04-2018 | 1 | E | 310 | 310 | 1 | |
18-04-2018 | 1 | F | 507 | 475 | 0 | |
19-04-2018 | A | 275 | 38 | 0 | ||
19-04-2018 | B | 190 | 47 | 0 | ||
19-04-2018 | C | 1164 | 626 | 0 | ||
19-04-2018 | D | 339 | 305 | 0 | ||
19-04-2018 | E | 310 | 266 | 0 | ||
19-04-2018 | F | 507 | 430 | 0 | ||
20-04-2018 | A | 275 | 41 | 0 | ||
20-04-2018 | B | 190 | 45 | 0 | ||
20-04-2018 | C | 1164 | 313 | 0 | ||
20-04-2018 | D | 339 | 146 | 0 | ||
20-04-2018 | E | 310 | 388 | 1 | ||
20-04-2018 | F | 507 | 475 | 0 |
The expected result is inserted in the column DaysSoldOut.
Can you please show what is ur expected result in that pic