Please help me calculate the future number of cows using DAX

[TineHejgaard]

I am stuck with a calculation i Power BI. The Power BI visualises future number of cows for the next 12 weeks and the surplus number of cows to help the farmer optimize the capacity of the stable. A parameter is used for the enduser to provide the max capacity of the stable and then a measure must calculate (from week to week) how many cows are surplus and must be sold and the new number of cows after selling.

I have created a very simple example in Excel, where it is very easy to make the calculation, my problem is to translate it to DAX.

(Example is placed on one drive Power BI Community)

Here is the Power BI frontend (in Danish, sorry) where the calculation is wrong, but so you get the idea.

[Tine_Hejgaard]

Hi Tom

We have sudently discovered that there unfortunately is an error in the calculation, som if the parameter value "Stable Size" is set to 11 or 13 the calculation does not work? 10 and 12 works fine. Do you have any idea why this is? 

Kind regards Tine

[Tine_Hejgaard]

never mind - we have solved it in the model and now it works. 

[TomMartens · Accepted solution]

Hey @TineHejgaard ,

here you will find a pbix that contains two measures (a pure DAX solution, allowing a dynamic stable-size):
https://tommartens-my.sharepoint.com/:u:/g/personal/tom_minceddata_com/EUYjyQrV1FpJvKg7Li9dwjEB80zoQ...

Due to the nature of the problem or the solution that can be created using Excel, the DAX of the Surplus measure is more complex. The surplus also contains a "shortcut." This shortcut assumes that the data type of the "Original number of cows" column is an Integer.
If the above-mentioned assumption is not correct, the inner calculation of the column [s] has to be adapted and will become more complex. The reason for this is that the PATHITEM function can only extract TEXT or INTEGER types from a given path. More complex means the ADDCOLUMNS( GENERATESERIES( .. ) ) will grow 😉 The complexity might also outgrow the time I will spend on interesting questions here, but I'm sure Just and I will find some kind of solution.

Nevertheless, here is a screenshot from the table I created from your sample data (the Excel file) and the two measures:

Hopefully, this provides what you are looking for.

Regards,

Tom

[TineHejgaard]

Thank you so much for the help! I appeciate it a lot and so will the Danish farmers 🙂 The original number of cows is an integer, so it should work. Kind regards Tine

[v-yadongf-msft]

Hi @TineHejgaard ,

Unfortunately, after some hard work on my part, DAX doesn’t seem to do what you want in Excel.

DAX has to be calculated based on an actual column and cannot implement circular dependency like Excel (in my attempt).

This is my DAX:

New number of cows = 
VAR ACUM = 
CALCULATE(
    SUM('Input data'[Surplus]),
    FILTER(ALL('Input data'),'Input data'[Week]<=EARLIER('Input data'[Week]))
    )
VAR CUR_WEEK = 'Input data'[Week]
VAR PRE_WEEK =CUR_WEEK-1
VAR PRE_ROW = SUMX(FILTER(ALL('Input data'),'Input data'[Week] = PRE_WEEK),ACUM)
 VAR _MINUS = 'Input data'[Original number of cows]-PRE_ROW
RETURN
_MINUS

Surplus = IF(
    'Input data'[New number of cows]-10 < 0, 0,
    'Input data'[New number of cows]-10
    )

However, I get the following error:
<ccon>A circular dependency was detected: Input data[New number of cows], Input data[Surplus], Input data[New number of cows].</ccon>

If there is any progress in the problem, please contact us in time.

Thanks for your efforts & time in advance.

Best regards,

Yadong Fang

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

[daXtreme]

Hi @TineHejgaard 

If you don't get replies to your questions for a long time, then probably people have a hard time understanding what it is you want. Please try to follow these guidelines to make sure your question gets the best chance of being answered. Cheers.