Calculate Queue Size in Power BI

[gedwards1988]

Hi all,

I am having a mind blank for a relatively simple calculated column in Power BI.  I am trying to clculate the queue size, defined by:

week_num   hub_referrals    starts    Queue

2021-41        17                    0             17         (hub_referrals - starts) + previous Queue = (17 - 0) + 0 = 17

2021-42         17                   18           16         (hub_referrals - starts) + previous Queue = (17 - 18) + 17 = 16

2021-43         14                   22           8           (hub_referrals - starts) + previous Queue = (14 - 22) + 16 = 8

...

...

I have tried creating an index column then using that to get the value fo the previous row, then addign it to my formula, but it is not working dynamically.  Any pointers on topics I should look at, or previous solutions I coudl draw inspiration from.

thanks

Gareth

[wdx223_Daniel · Accepted solution]

a calculated column in dax:

newcolumn=sumx(filter(table,table[week_num]<=earlier(table[week_num])),table[hub_referrals]-table[starts])

M code in Power Query:

NewStep=Table.FromRecords(List.Accumulate(Table.ToRecords(PreviousStepName),{{},0},(x,y)=>{x{0}&{y&[Queue=y[hub_referrals]-y[starts]+x{1}]}, y[hub_referrals]-y[starts]+x{1}}){0})

[gedwards1988]

Thanks this worked a treat, slight error in my formul and M code but now works perfectly.  Thanks