Calculate inventory days, but only when there is inventory QTY

[Morten_DK]

I am trying to calculate inventory days (meaning how long time will the item theoretically be on stock from now on, based on history) with this measure:

Inventory Days2 =

calculate(

    DIVIDE([Inventory Valuation]*90,[Outflow Value 3M]),

    [Inventory QTY]<>0

            )

But then I get this error:

Inventory QTY is a measure in itself:

Inventory QTY =

CALCULATE (

    SUM ( 'Value Entries'[Item Ledger Entry Quantity] ),

    'Value Entries'[Item Ledger Entry Type] < 7,

    CALCULATETABLE (

        FILTER ( ALL ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )

    )

)

I have tried with "if" and "sumx", but they don't seem to get the grand total right.

Right now I get this result:

Item	Inventory valuation	Outflow Value 3M	Inventory QTY	Inventory days
A	100	150	3	60
B	200	150	4	120
C		150	0	0
Total	300	450	7	60

But the issue there is, that for Item "C" the outflow value of 150 does not have any influence really on the row for item "C", but on the total the 150 is included, for calculation purpose this would be the right result regarding inventory days:

Item	Inventory valuation	Outflow Value 3M	Inventory QTY	Inventory days
A	100	150	3	60
B	200	150	4	120
Total	300	300	7	90

[Anand24]

Hi @Morten_DK ,
Can you put some sample data here with expected result and current result?

Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!! Proud To Be a Super User !!! LinkedIn

[Morten_DK]

I have updated the original post with some sample data, hope that helps 🙂

[Anand24]

@Morten_DK ,
Here's your solution DAX:

Inventory Days2 = 
DIVIDE(
    CALCULATE(
        SUM('Table (3)'[Inventory valuation]), 
        'Table (3)'[Inventory valuation] <> BLANK()
    )*90,
    CALCULATE(
        SUM('Table (3)'[Outflow Value 3M]), 
        'Table (3)'[Inventory valuation] <> BLANK()
    )
)

Please make other changes accordingly because I have created the DAX according to the data your provided.

Result:

Give a Thumbs Up if this post helped you in any way and Mark This Post as Solution if it solved your query !!! Proud To Be a Super User !!! LinkedIn

[Morten_DK]

Thanks for the feedback. I can't get the proposed solution working, as "Inventory valuation" is also a measure

Inventory Valuation =

CALCULATE (

    [Total GL INV],

    'Value Entries'[Item Ledger Entry Type] < 7,

    CALCULATETABLE (

        FILTER ( ALL ( 'Date' ), 'Date'[Date] <= MAX ( 'Date'[Date] ) )

    )

)

My data is that I have the general ledger entries, where I need to sum up all transactions until given date, but that also means that I can get a sum of the transactions equal to zero. That can for instance happen if I had stock value in march 2022 and then consumption of all stock in april 2022, so looking at the data in december 2022 then the sum would be zero.