How to calculate average inventory in power bi?

user_14301483 0
Viewed 354

I would like to calculate average inventory as per month. I have used date filter in the report.

We have average inventory formula as (beginning inventory + closing inventory)/2

I have following logic to calculate average inventory. If we choose month as a march then the amount of the beginning inventory will be the inventory of the march and the amount of the closing inventory will be the inventory of the the last month and we need to sum these beginning and closing inventory and then take averages of them.

So how can we write above logic in DAX expression?

I have attached the data with expected output.

Please click on this link to see the data with the expected output.

powerbi
1 Answers
Average Inventory =
VAR _latestdate =
    CALCULATE ( MAX ( 'Table'[Date] ), ALL ( 'Table'[Date] ) )
RETURN
    (
        SUMX ( 'Table', 'Table'[Inventory Sum Amount] )
            + CALCULATE (
                SUMX ( 'Table', 'Table'[Inventory Sum Amount] ),
                FILTER ( ALL ( 'Table' ), _latestdate = 'Table'[Date] )
            )
    ) / 2

You should have a proper date formatted column...

Output

user_52602650
Related