Forum Discussion

karthiki_913's avatar
karthiki_913
New Member
3 years ago

Iventory provision through DAX

    I have encountered a scenario where I need to calculate the inventory provision using a Dax measure based on shelf life and the number of days passed. However, I am currently at a loss fo...
Anonymous's avatar
Anonymous
Not applicable
3 years ago

Hi karthiki_913 ,

 

I suggest you to try code as below to create a measure.

Measure = 
VAR _SELECTION =
    SELECTEDVALUE ( 'End Date of Month'[Date] )
VAR _VIRTUAL_TABLE =
    ADDCOLUMNS (
        'SKU List',
        "Provision",
            VAR _REM_IN_Provision =
                CALCULATE (
                    MIN ( Povision[Rem Shelf Life] ),
                    FILTER ( Povision, Povision[Rem Shelf Life] >= EARLIER ( [REM SHELF LIFE] ) )
                )
            VAR _DateDiff =
                DATEDIFF ( [Arrvial Date], _SELECTION, DAY )
            RETURN
                CALCULATE (
                    MAX ( Povision[Provision] ),
                    FILTER (
                        Povision,
                        Povision[Rem Shelf Life] = _REM_IN_Provision
                            && Povision[Days passed] <= _DateDiff
                    )
                )
    )
RETURN
    SUMX ( _VIRTUAL_TABLE, [Provision] )

Result is as below.

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.