Forum Discussion

Bart_1989's avatar
Bart_1989
Frequent Visitor
9 years ago

Inventory age based on batch manufacturing date

Hi everyone,   I have a table with inventory transactions that I use to calculate movements as well as inventory levels per day.   Quantity:=SUM(InventTrans[Qty])   Stock Quantity:=CALCULATE( ...
Need Help
Bart_1989's avatar
Bart_1989
Frequent Visitor
9 years ago

No one?

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Bart_1989

 

Hi Bart,

 

I tried to achieve this in your current report. Failed. How about creating a new table? I attach the PBIX here: https://1drv.ms/u/s!ArTqPk2pu-BkgQL-fm4sRKZ3W8Y8.

Notes: 1. The new table is: FinalResult and the new report is: FinalResult. Other tables and reports are only for your information.

2. The formula of creating a new table.

 

FinalResult =
SUMMARIZE (
    ADDCOLUMNS (
        FILTER (
            CROSSJOIN ( 'DatePhysical', 'InventTrans' ),
            'DatePhysical'[Dates] <= 'InventTrans'[Date]
                && 'DatePhysical'[Dates] >= 'InventTrans'[ManufacturingDate]
        ),
        "days", DATEDIFF ( [ManufacturingDate], [Dates], DAY )
    ),
    [InventBatchID],
    [Dates],
    [days]
)

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

  • Bart_1989's avatar
    Bart_1989
    Frequent Visitor
    9 years ago

    Hi v-jiascu-msft,

     

    Thanks for your answer and your effort!

    I have meetings for the next upcoming days, but will surely try your solution as soon as I can. I will get back to you when I did!

     

    Regards,

    Bart

    • Bart_1989's avatar
      Bart_1989
      Frequent Visitor
      9 years ago

      Hi v-jiascu-msft,

       

      Unfortunately, I can't get it to work and I decided to write a state table in SQL instead.

      Cross joining DatePhysical and InventTrans is a way I don't really want to go in, as the InventTrans table can contain millions of records.

       

      I want to thank you a lot for your help though.

       

      Regards,

      Bart

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        Bart_1989

         

        Hi Bart,

         

        My pleasure. Cross joining with a filter may work. I tested it with a large table once. If you want to try, backup your work first.

         

        Best Regards!

        Dale