Iventory provision through DAX

[karthiki_913]

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 for ideas on how to solve this issue in a professional manner. To tackle this problem, there are three tables that are available for reference.

The first table is the Provision table, which is based on shelf life and the remaining days.

The second table is the SKU list, containing four columns: SKU name, Agency name, Remaining Shelf life, and Arrival date. L

Lastly, there is Table 3, which provides dates by month end.

In Table 2, my objective is to create a measure that is dependent on the "selected date" from the date table. This measure should calculate based on a specific logic at the row level.

First check the remaining shelf life, If it is less than 365 days, the initial three rows should be chosen from the provision table. After selecting these rows, the next step is to calculate the number of days that have passed since the selected date of arrival. This calculation will be based on the difference between the selected date and the arrival date. Once these factors are taken into account, the provisions that meet the criteria of remaining shelf life and days passed can be displayed in the SKU list.

[v-rzhou-msft]

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.

[lbendlin]

the initial three rows should be chosen from the provision table. 

DAX has no concept of that. You need to indicate precisely what you mean by that and how to calculate it.

Power BI is not an inventory management tool.