How much available Qty I can expect to be consume based on "Average month sales" till remaining days expire

DAX DAX Calculations
1

Please find the attached pbix file for reference and calculations.

How much available Qty I can expect to be consume based on “Average month sales” till remaining days expire.

How much Qty expected to be expire after consuming till expiry days.

Available qty split in two columns = Expected to be consume & expected to be expire

Both totals should be match with available qty

sample report consumption.pbix (129.7 KB)

2

I would be grateful if someone could resolve this please.

3

I don’t know if I understand, but see if it helps
sample report consumption.pbix (130.6 KB)

image
image762×319 14.9 KB

4

Thanks vilmar for responding. The solution that you have shared seems like numbers are not correct on rows level.

I have split in two columns “Expected to be consumed” & “Expected to Expire” in rows level the numbers seem correct, but totals need to fix it.

I would be grateful if you or someone could resolve this, please.

Updated file enclosed
sample report consumption.pbix (137.0 KB)

5

see if it helps

Qty Expected to be Consume = 
SUMX (
    SUMMARIZECOLUMNS( Products[Products], Stock[Expiry Date] ),
    IF (
        [Diff near to expiry - Expected consume till remaning days] <= 0,
        [Available Qty],
        [Expected Qty Consume till remaining days]
    )
)

image
image1207×351 26.6 KB

6

Brilliant Vilmar, Thank you so much. Really you have made my weekend.

Last queries with relevant to this context.

How can we split Stock Amount in two breakups based on “Qty expected to be consume” and “Qty expected to be expire” and both totals should be equal to Stock Amount.

Please find enclosed the updated file for your reference.

sample report consumption.pbix (138.5 KB)

7

I don’t know if I understand very well, but see if it helps.

_Medida = 
[Stock Amount]
    * DIVIDE (
        [Qty Expected to be Consume],
        CALCULATE (
            [Qty Expected to be Consume],
            REMOVEFILTERS ( Products[Products] ),
            REMOVEFILTERS ( Stock[Expiry Date] )
        )
    )

image
image771×340 19.6 KB