How to calculate average inventory in power bi

Question

I've a scenario, I need to calculate average inventory per months where we have formula of average inventory is (beginning inventory + closing inventory)/2. if I select any month, then the beginning inventory should have the inventory amount of selected month and closing inventory should have the inventory amount of the last month. After that we will use the above listed formula for calculation. Can anyone help me to achieve this end result in power bi?

Answer 1

Hi @There,

Is there any index field in your table? If this is a case, you can write a formula to use the index to find out the corresponding begin value and end value, then use them to get the average. 

AVG Measure =
VAR _start =
    MINX ( ALLSELECTED ( Table[index] ), [index] )
VAR _end =
    MAXX ( ALLSELECTED ( Table[index] ), [index] )
RETURN
    DIVIDE (
        LOOKUPVALUE ( Table[Amount], Table[index], _start )
            + LOOKUPVALUE ( Table[Amount], Table[index], _end ),
        2,
        BLANK ()
    )

Otherwise, you need to add an index column first.(power bi data model does not contain a row/column index to find out first or last row value).

Hope this helps!!

If you are interested in learning Power BI, check out Power BI Course online now!