Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
karthiki_913
New Member

Iventory provision through DAX

dax.JPG

 

 

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.

2 REPLIES 2
v-rzhou-msft
Community Support
Community Support

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.

vrzhoumsft_0-1696406753762.png

 

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
Super User
Super User

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.

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors