Inventory as of a prior month

[skrishnaswamy]

Hi All,

I need to calculate the Inventory based on the number of units that moved from Wholesale to Retail. I have a table that has the following 

WholeSale Date | Retail Date | Units 

----------------------------------------

1/02/2020         |                     | 1          
1/15/2022         |   1/20/2022  | 1    
1/20/2022         |    2/05/2022 | 1

The first and third records  are  in inventory as of Jan 31st, 2022 but not the second since it got sold before the  end date and was not in the pipeline as of the end of the month ( Jan 31st 2022) 

When an item enter the system, the wholesale date is filled and the retail date is not filled until the item is sold by the retailer.

So this means that if the Retail date is blank, it is still in the Inventory. As of today, if I pick sum the units which have retail date blank then I will get the Inventory. 

The problem is how do I get the inventory as of a prior month ( or date) i.e Jan 2022. The records that need to be considers are from Jan 1, 2020  ( as I do not need to go back more than 3 yrs).

I am able to get the result by filtering the rows in a visual.

How can I create a measure to achieve the same result ?

See screenshot below. The inventory is 10,245.

I can provide a .pbix file for anyone that needs data 

Thanks

SK

[skrishnaswamy · Accepted solution]

Xiaoxin,

I got it to work. There was a slight error in the conditions in the AND and OR section.
Below is the final formula that works and the screenshot is shown below.

formula =

VAR currDate =

    MAX ( V_INVENTORY_BENCHMARK[WHOLESALE_DATE] )

VAR NextMonthDate =

    DATE ( YEAR ( currDate ), MONTH ( currDate ) + 1, 1 )

VAR _start =

    DATE ( YEAR ( TODAY () ) - 3, MONTH ( TODAY () ), DAY ( TODAY () ) )

    //DATE(2019,12,31)

RETURN

    IF (

        currDate >= _start,

        CALCULATE (

            SUM ( V_INVENTORY_BENCHMARK[UNITS] ),

            FILTER (

                ALLSELECTED ( V_INVENTORY_BENCHMARK ),

                AND (_start < 'V_INVENTORY_BENCHMARK'[WHOLESALE_DATE]  , 'V_INVENTORY_BENCHMARK'[WHOLESALE_DATE] < NextMonthDate   )

                    && OR ( V_INVENTORY_BENCHMARK[RETAIL_DATE] = BLANK (), 'V_INVENTORY_BENCHMARK'[RETAIL_DATE] >= NextMonthDate )

            )

        )

    )

Many thanks for making this work. I have to work on getting the median pipelength with different conditions but I understand the logic of the calculations now. 

Once again many thanks

SK

[skrishnaswamy]

Here is the link 

https://nmma-my.sharepoint.com/:u:/g/personal/skrishnaswamy_nmma_org/EduSK7VQ0SFJijlEMMC6YFcBCdqz9yf...

[skrishnaswamy]

Thanks for your prompt reply. The number is close but not exact when I apply the filters to the table and display it in a table visual.  Uploading the .pbix file that i used to create this again

[v-shex-msft]

Hi @skrishnaswamy,

I'd like to suggest you write a variable to extract the current row date values, then you can use it with the date function to calculate the previous date.
After these steps, you can write an expression to filter and summary records on your fact table based on date values. (these calculations can be dynamic changes based on the current row context date)

formula =
VAR currDate =
    MAX ( Table[WholeSale Date] )
VAR prevDate =
    DATE ( YEAR ( currDate ), MONTH ( currDate ) - 1, DAY ( currDate ) )
VAR _start =
    DATE ( YEAR ( TODAY () ) - 3, MONTH ( TODAY () ), DAY ( TODAY () ) )
RETURN
    IF (
        currDate >= _start,
        CALCULATE (
            SUM ( Table[Sales] ),
            FILTER (
                ALLSELECTED ( Table ),
                AND ( [WholeSale Date] <= prevDate, [WholeSale Date] >= _start )
                    && OR ( [Retail Date] = BLANK (), [Retail Date] >= prevDate )
            )
        )
    )

Regards,

Xiaoxin Sheng

[skrishnaswamy]

Please download the .pbix from the following link 

https://nmma-my.sharepoint.com/:u:/g/personal/skrishnaswamy_nmma_org/EQgoT4HtKCBAlc8UDd21baYB7YAho6C...