Forum Discussion

pejczi's avatar
pejczi
Frequent Visitor
8 years ago
Solved

FIFO - Stock Calculation

Dear all, I would like to ask you how to calculate stock if the previous row is negative value. For example : First photo (on the left) shows my example values. I would like to create a new measure...
  • pejczi's avatar
    pejczi
    8 years ago

    Okay, I found a solution, maybe it'll help someone :

     

    VolumeStock = 
CALCULATE (
    [Volume Left]; 
    FILTER (
        ALL ( Hedge ); 
        'Hedge'[Hedge Year] = MAX ( 'Hedge'[Hedge Year] )
            && Hedge[Hedge Price [zł]]] <= MAX ( Hedge[Hedge Price [zł]]] )
    )
)

    and then another measure :

Volume Stock = IF([VolumeStock]<0;0;[VolumeStock])
pejczi's avatar
pejczi
Frequent Visitor
8 years ago

Thank you for your answer.

 

Apparently, it sums wrong rows - it sums from the volume left from first row of each group, instead of every single from one group until its positive (basing on the article you sent).

 

VolumeStock = 
IF (
    MIN ( Hedge[Hedge Year] ) <= CALCULATE ( MAX ( Hedge[Hedge Year] ); ALL ( Hedge ) );
CALCULATE([Volume Left];FILTER(ALL(Hedge[Hedge Year]);(Hedge[Hedge Year])<=MAX(Hedge[Hedge Year]))))

 

pbi

pejczi's avatar
pejczi
Frequent Visitor
8 years ago

Okay, I found a solution, maybe it'll help someone :

 

VolumeStock = 
CALCULATE (
    [Volume Left]; 
    FILTER (
        ALL ( Hedge ); 
        'Hedge'[Hedge Year] = MAX ( 'Hedge'[Hedge Year] )
            && Hedge[Hedge Price [zł]]] <= MAX ( Hedge[Hedge Price [zł]]] )
    )
)

and then another measure :

Volume Stock = IF([VolumeStock]<0;0;[VolumeStock])