Recursive Measures Help

Hi All,

Need help with one scenario to Recursively Calculate Values. Below are sample values

Metrics 6/1/2020 6/8/2020 6/15/2020 6/22/2020 6/29/2020 7/6/2020 7/13/2020
Opening Bal 1357 0 0 0 0 0 0
InQualityInsp 1480 0 0 0 0 0 0
Totaldemandquantity 153 105 741 121 69 304 576
Totalsupplyquantity 0 296 0 0 0 0 0

My Requirement is to create two Measures as below

  1. Opening Inventory = If Week1 then Opening Bal else Closing Inventory (Last Week) + InQualityInsp (Last Week)
  2. Closing Inventory = Opening Inventory + Totalsupplyquantity - Totaldemandquantity

Expected Outcome:

Opening Inventory 1357 2684 2875 2134 2013 1944 1640
Closing Inventory 1204 2875 2134 2013 1944 1640 1064

As Opening Inventory and Closing Inventory measures are interdependent, so getting Circular Dependency error.

Attached sample Data and PBIX
Test.pbix (39.7 KB)
Sample Data.xlsx (8.5 KB)

@BrianJ , @Melissa - Please check.

Note: Field in Sample Values are Branched Measures , I am primarily looking for measure. If Measure not possible then Calculated Column I can also try.

Thanks
Ankit J

@ankit,

I’ll take a look and see what I can come up with. Coincidentally, I was just reviewing the Debugging and Optimization chapter of the Greg Deckler book I mentioned the other day, and he’s got a section on addressing circular dependencies.

Don’t know if a Power Query-based solution might be acceptable, but I’ve been playing around a little with some of the recursive concepts from this video:

Will get back to you later this evening…

  • Brian

@BrianJ and @ankit,

If the aggregation and report level is weekly, look into Power Query Group by with the optional parameter GroupKind.Local. An example can be found in this post by Chris Webb.

I hope this is helpful.

Hi @BrianJ and @Melissa - Thanks Both for your inputs. I will look into the blogs. As mentioned, Sample data is basically measures that are branching to lot other measures.

So, I am primarily looking for measures but if no option then I will try POC for PQ or CC.

Thanks
Ankit J

@ankit,

I think I figured out how to break the recursive loop here and still get to the correct results.

  1. Calculate cumulative totals for all four metrics
  2. Closing Inventory Balance = Cumul Opening Balance + Cumul Total Supply - Cumul Total Demand (no recursion, and all calculated on same row)
  3. For Opening Inventory Balance, you will need to write measures to retrieve the previous week’s Closing Inventory Balance and prev week’s InQualityInsp, but I’m sure that won’t be a problem for you.

I actually worked this out in Excel (heresy!, I know…) and have attached my working file.

Really interesting problem. I hope this is helpful to you.

1 Like

Thanks @BrianJ for the efforts and inputs. Atleast now I have something to work with rather than being completely blank :slight_smile: I will work on the suggestions and will come back with the updates.

Thanks
Ankit J

Thanks @BrianJ. Your solution totally worked. Made few adjustments though as below.

  1. Instead of calculating Closing Inventory, I calculated Opening Inventory first using similar Running Balance measures
  2. Once Opening Inventory was calculated, Closing was simple.

Thanks again for your help on this.
Ankit J

@ankit,

Very glad to help – I’m thrilled to hear this worked well for you. I can definitely see why this one gave you problems. I tried a bunch of different approaches and algebraic substitutions before getting to this one, and just had to mull it over for a couple of days.

Really unique problem that I haven’t seen before – enjoyed working with you on this one.

  • Brian
1 Like