topic Re: Calculating plan accuracy, circular reference in DAX Commands and Tips

Calculating plan accuracy, circular reference

bidgeir — Mon, 08 Mar 2021 12:40:49 GMT

Hi all

I´m trying to calculate plan accuracy where I can "use" orders from +-1 week to fulfill the plan. As you can see in the image below I have 13 weeks. In those 13 weeks I have plan in column C and orders in column B. Columns A, B and C are my data, the rest is what I want to calculate. In column E I have calculated the Orders +-1 week but taking into account if there are any leftover from previous period. The problem is that the leftover are reliant on Orders +-1 week. This is possible in Excel because in the Orders +-1 week (column E) I reference leftover (column F) from the row above. So the leftover formula for line 3 is E3-C3 and the Orders +-1 week for line 3 is B4+IF(F2>SUM(B2:B3),SUM(B2:B3),IF(F2>0,F2,0)).

In Power BI it´s not possible to reference a column/measure that relies on the source column/measure.

The goal are column G, H and I using only columns A, B and C. So my main goal is column H line 15 (the total accuracy) but I would like to calculate accuracy pr. line and covered batches as well.

The datasource is not Excel and not in this exact format but it´s reprecentative of the data source.

If anyone has an idea about how to go on about solving this in Power BI I would be very greatful. I don´t care if the solution is in Power Query or DAX. You can download the Excel file with this example and another one here: https://tinyurl.com/4b55kaxh

I have just fiddled a little with it in Power BI so I don´t have any PBIX to share.

/Ásgeir

Re: Calculating plan accuracy, circular reference

bertalan_ronai — Tue, 09 Mar 2021 09:40:06 GMT

Hi!

If you don't reference a measure but use the same DAX in the other measure as a variable then it might solve the referencing problem. I do admit it is just a quick guess on my part.

Re: Calculating plan accuracy, circular reference

bidgeir — Tue, 09 Mar 2021 10:12:00 GMT

I simple cannot see how that is possible. The calculation for one use the other one and vice versa. But maybe I´m just blocked in my mind after looking at it from a certain point of view for too long

Re: Calculating plan accuracy, circular reference

bidgeir — Wed, 10 Mar 2021 13:21:36 GMT

I have created a PBIX with the Excel imported if anyone prefers not to import then Excel data. You can find the PBIX here: https://tinyurl.com/yutx7st9

Re: Calculating plan accuracy, circular reference

bertalan_ronai — Wed, 10 Mar 2021 13:50:09 GMT

Hi!

I tried a measure. Check out my file please. https://datascientistbudapestcom-my.sharepoint.com/:u:/g/personal/bertalan_datascientistbudapest_com/EcwG0ZGNUCRCoj3MbGpkar8BkHBkhaVoPhKBHzZG6vRiVg?e=kidxQ2

Re: Calculating plan accuracy, circular reference

bidgeir — Mon, 15 Mar 2021 08:34:43 GMT

Good try but unfortunately not what I need. I have come to the conclusion that it´s not possible (at least with my knowledge) so I´m not going to calculate it in this way. I going for a less accurate but good enough method of looking at the total period instead of focusing on each week.