Forum Discussion
Calculated Column Self Referencing Itself at Prior Period
For a little bit of background on the problem, I have monthly data with various products. Products can span many months and many products can be active during the same months. Each product has a field with a maximum allocation. I need to pull in the values to allocate from another table and allocate these values to a product up to the point where the maximum is met.
The problem I am running into is circular dependancies. I can create a running sum of the allocation up to the prior period. I can not reference this column when trying to actually pull in the values that would be added to the running sum even though it is a prior period. I built out the below in excel to demonstrate a simpler version what I am trying to replicate within powerBI.
I can not use an index because the running sum is partitioned by the different products and the prior indexed row could be a different product. Any pointers would be much appreciated. Thanks!
| Account | Product | Date | Max Quantity | Monthly Quantity to Assign | Allocated Quantity | Previous Month Running Sum | ||
| Account 1 | Product 1 | 1/31/2021 | 40 | 10.00 | 10 | =IF(H2<D2,E2,0) | ||
| Account 1 | Product 1 | 2/28/2021 | 40 | 10.00 | 10 | =IF(H3<D3,E3,0) | 10 | =SUM($F$2:F2) |
| Account 1 | Product 1 | 3/31/2021 | 40 | 10.00 | 10 | =IF(H4<D4,E4,0) | 20 | =SUM($F$2:F3) |
| Account 1 | Product 1 | 4/30/2021 | 40 | 10.00 | 10 | =IF(H5<D5,E5,0) | 30 | =SUM($F$2:F4) |
| Account 1 | Product 1 | 5/31/2021 | 40 | 10.00 | 0 | =IF(H6<D6,E6,0) | 40 | =SUM($F$2:F5) |
| Account 1 | Product 1 | 6/30/2021 | 40 | 10.00 | 0 | =IF(H7<D7,E7,0) | 40 | =SUM($F$2:F6) |
| Account 1 | Product 1 | 7/31/2021 | 40 | 10.00 | 0 | =IF(H8<D8,E8,0) | 40 | =SUM($F$2:F7) |
| Account 1 | Product 1 | 8/31/2021 | 40 | 10.00 | 0 | =IF(H9<D9,E9,0) | 40 | =SUM($F$2:F8) |
| Account 1 | Product 1 | 9/30/2021 | 40 | 10.00 | 0 | =IF(H10<D10,E10,0) | 40 | =SUM($F$2:F9) |
| Account 1 | Product 1 | 10/31/2021 | 40 | 10.00 | 0 | =IF(H11<D11,E11,0) | 40 | =SUM($F$2:F10) |
Hi, Anonymous
Powerbi doesn't support recursion, but your needs are not completely recursive, it should be supported.
How about this:
Allocated Quantity = VAR TOTAL = SUMX ( FILTER ( Table, [Date] <= EARLIER ( Table[Date] ) && [Product] = EARLIER ( Table[Product] ) ), [Monthly Quantity to Assign] ) VAR maxquantity = MAXX ( FILTER ( Table, [Product] = EARLIER ( Table[Product] ) ), [Max Quantity] ) RETURN IF ( TOTAL > maxquantity, 0, TOTAL )Best Regards,
Community Support Team _ Janey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
- amitchandak
Super User
Anonymous , Running sum a new column
sumx(filter(Table, [Date] <= earlier([Date])) , [Allocated Quantity])
Thank you for the response. I need it to be running sum prior to this month and at the product level. I can manipulate the code you provided to get that.
sumx(filter(Table, [Date] < earlier([Date]) && [Product] = Earlier([Product])) , [Allocated Quantity])However the issue is that I need to check this running sum within the "Allocation Column". The two work together.
Psuedo code for Allocation would be ...
If running sum < max allowed
then Monthly Quantity to Assign
else 0
This methodology however gives me a circular dependancy error since the running sum is a derivation of the previous months "Allocation".
- v-janeyg-msft
Community Support
Hi, Anonymous
Accordint to your description, I think you can create a column.
Like this:
Allocated Quantity = VAR TOTAL = SUMX ( FILTER ( Table, [Date] <= EARLIER ( [Date] ) ), [Monthly Quantity to Assign] ) RETURN IF ( TOTAL > [Max Quantity], 0, TOTAL )Best Regards,
Community Support Team _ Janey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
