create a calculative and foracsted measure

[hchauhan556]

• I have different table names as current inventory with column name import date, product code quantity, and other table demand with column names product code, date, and forecasted qty and another table name as invoicevolumereport with column name as product code, invoice date, invoice qty and another table name as packaging column name as date, product code, qty, and table name as code with column name product code this code table is made from all the existing table by picking their product code so column product code has all product codes from an existing table and table name as date column name date this has all dates from all existing tables. now if we are taking data from different tables. can we create a table in visuals where there will be columns named as a finished product, the sum of forecasted qty, the sum of planned production and there will be two measures named as adjusted inventory and ending qty both play important roles in their calculations as both will be dependent on each other I also want dax formula for a measure should work on a date if current qty where it should do play as forecast if the current month inventory doesn't have any value it should pick up the ending inventory of previous month and perform the calculations of ending inventory of the current month accordingly and if the current month has any value it should pick that current value and perform the ending inventory calculation of that month accordingly...     
• In the attached screenshot data is extracted from diff tables but what am struggling over here is that my measure name as   adjusted inventory is not showing correct output its taking the ending inventory of self month not the previous month for example in feb. ending inventory is 89314 and in march if current inventory is = 0 then Inventory measure should pick up the vale of previous month which is 89314 and all calculations should be performed accordingly and same goes for remaining months.   here is the dax code for inventory measure  :-   
  Ending inventory =

  VAR InvoiceQty = SUM('InvoiceVolumeReport'[Invoice Quantity])

  VAR ForecastedQty = SUM('Demand'[ForecastedQty])

  VAR CurrentQty = SUM('CurrentInventory'[Quantity])

  VAR PackagingValue = SUM('Packaging'[Value])

  
  

  RETURN

  IF (

      InvoiceQty > ForecastedQty,

      CurrentQty + PackagingValue,

      CurrentQty - ForecastedQty + InvoiceQty + PackagingValue

  )

  and dax code for =     

  Adjusted Inventory =

  VAR CurrentMonth = SELECTEDVALUE('CurrentInventory'[Import Date])

  VAR CurrentProductCode = SELECTEDVALUE('CurrentInventory'[Product Code])

  VAR CurrentQty = SUM('CurrentInventory'[Quantity])

  
  

  RETURN

  IF(

      CurrentQty = 0,

      CALCULATE(

          [Ending Inventory],

          'CurrentInventory'[Product Code] = CurrentProductCode,

          'CurrentInventory'[Import Date] = EOMONTH(CurrentMonth, -1)

      ),

      CurrentQty

  )

  can you please help me in this

[hchauhan556]

Hi 

I have already added the dax code for ending inventory in original post

[v-tangjie-msft]

Hi @hchauhan556 ,

Please try this:

Adjusted Inventory =
VAR CurrentMonth = SELECTEDVALUE('CurrentInventory'[Import Date])
VAR CurrentProductCode = SELECTEDVALUE('CurrentInventory'[Product Code])
VAR CurrentQty = SUM('CurrentInventory'[Quantity])

RETURN
IF(
    CurrentQty = 0,
    CALCULATE(
        [Ending Inventory],
        'CurrentInventory'[Product Code] = CurrentProductCode,
        Month('CurrentInventory'[Import Date]) = Month(EOMONTH(CurrentMonth, -1))
    ),
    CurrentQty
)

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[hchauhan556]

Hi 

the output from your code is wrong:

output is 

Here is the sample of expected output:

as you can see from 24-01 the ending inventory was 105,960 and in 24-02 the current inventory was 0 so automatically the current inventory of 24-02 became 105,960 and performed other calculations.

the table which I have are

1. CurrentInvntory= important columns are highlighted in yellow in fig 1.1

                                                                       Fig. 1.1

2nd table is named Demand which is the forecast table. Important columns are highlighted in yellow in fig 2.1

                                                                Fig. 2.1

3 table is InvoiceVolumeReport. Important columns are heightened in Fig. 3.1

                                                 Fig. 3.1

4 table is planned production. important fields are highlighted below in Fig. 4.1 and column value means QTY.

and I have created some tables with primary keys like tab;e name material id which has material code from all tables and table name date which has dates from all tables that can be used to create relations among them.

These all are tables of sample data 

[v-tangjie-msft]

Hi @hchauhan556 ,

Please try this:

Adjusted Inventory =
VAR CurrentMonth =
    SELECTEDVALUE ( 'CurrentInventory'[Import Date] )
VAR CurrentProductCode =
    SELECTEDVALUE ( 'CurrentInventory'[Product Code] )
VAR CurrentQty =
    SUM ( 'CurrentInventory'[Quantity] )
RETURN
    IF (
        CurrentQty = 0,
        CALCULATE (
            [Ending Inventory],
            FILTER (
                ALL ( 'CurrentInventory' ),
                'CurrentInventory'[Product Code] = CurrentProductCode
                    && MONTH ( MAX ( 'CurrentInventory'[Import Date] ) )
                        = MONTH ( EOMONTH ( 'CurrentInventory'[Import Date], +1 ) )
            )
        ),
        CurrentQty
    )

Measure 2 = SUMX(VALUES('CurrentInventory'[Import Date]),[Adjusted Inventory])

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[hchauhan556]

Hi Neeko Tang

You have taken the ending inventory as a column but in my case, I need to find out the ending inventory also.

KR

Himanshu

[v-tangjie-msft]

Hi @hchauhan556 ,

There doesn't seem to be any mention in your original post of the logic required for [ENDING INVENTORY].

In order to solve your new problem , please create a new case and describe your logic and expected results in detail, and mark helpful answers to the problems described in the current case as solutions. Thanks in advance.

Best Regards,

Neeko Tang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

[hchauhan556]

as you can see in attached screenshot I have already added ending inventory code in main post