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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
skrishnaswamy
Frequent Visitor

Inventory as of a prior month

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.

skrishnaswamy_0-1661888140411.png

 

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

 

Thanks

SK

 

 

1 ACCEPTED SOLUTION
skrishnaswamy
Frequent Visitor

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 )
            )
        )
    )





skrishnaswamy_0-1662418056950.png

 

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

 

View solution in original post

5 REPLIES 5
skrishnaswamy
Frequent Visitor

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 )
            )
        )
    )





skrishnaswamy_0-1662418056950.png

 

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
Frequent Visitor
skrishnaswamy
Frequent Visitor

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

 

skrishnaswamy_0-1662390520930.png

 

v-shex-msft
Community Support
Community Support

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

Community Support Team _ Xiaoxin
If this post helps, please consider accept as solution to help other members find it more quickly.
skrishnaswamy
Frequent Visitor

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

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