Loop though Iteration based on another column

[zenton]

Hi,

Problem 1
I need help with calculating the Bay in Use column.

Bay in u\Use starts with Bay1
When Column FULL = "Full" Bay in Use = Bay 2, then Bay 3, Bay 4

When Bay 4 is full the cycle starts again back to Bay 1
If possible I would like to make the Bays variable 1 to 4

Problem 2
If the bay is Full go to next bay. If no bays are available Bay in Use = "No Capacity"

The Bay is empty 13 working days after Full status as per Column Date Empty

Note: Running Totals and Loads are calculated in M Query

Thanks Rodney

[v-xinruzhu-msft]

Hi @zenton 

You can create two calculated column

Bay in use = var _filter=COUNTROWS(FILTER('Table',[Index]<=EARLIER('Table'[Index])&&[FUll]="Full"))
return IF(_filter>3,"Bay"&_filter+1-4,"Bay"&_filter+1)

Date Empty = IF([FUll]="Full",[Date]+13)

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[zenton]

Hi @v-xinruzhu-msft , Thanks fio your help. The calcultion works for the 1st itteration only. Idex row 43 should be Bay1

Thanks Rodney

[v-xinruzhu-msft · Accepted solution]

Hi @zenton 

You can refer to the following column

Bay in use = var _filter=COUNTROWS(FILTER('Table',[Index]<=EARLIER('Table'[Index])&&[FUll]="Full"))
return "Bay"&" "&MOD(_filter,4)+1

Best Regards!

Yolo Zhu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.