Forum Discussion

NewNow's avatar
NewNow
New Member
2 years ago
Solved

Tips for solving a Business Case - INVENTORY ALLOCATION TO CUSTOMER DEMAND

Hello, i need to solve the following Business Case:

i have a Supply - Inventory Table and Demand - Customer Table

 

I need to create a PBI model to solve the allocation problem between Demand And Supply, below an Excel Example. I would like to undestand how to create a Measure or calculated Column to create the OUTPUT Table Below.

Thank you

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi NewNow ,

     

    Of course, it is possible to improve the column definitions for calculations by using variables instead of EARLIER functions.


    For example

    Subcategory Sales Rank =
COUNTROWS(
FILTER(
Subcategory,
EARLIER(Subcategory[Subcategory Sales]) < Subcategory[Subcategory Sales]
)
) + 1

     

    The CurrentSubcategorySales variable stores the Subcategory Sales column value in the current row context, and the RETURN expression uses it in the modified filter context.

    Subcategory Sales Rank =
VAR CurrentSubcategorySales = Subcategory[Subcategory Sales]
RETURN
COUNTROWS(
FILTER(
Subcategory,
CurrentSubcategorySales < Subcategory[Subcategory Sales]
)
) + 1

     

    More details can be found in the documentation:Use variables to improve your DAX formulas - DAX | Microsoft Learn.

     

    Best Regards,

    Clara Gong

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

4 Replies

  • NewNow's avatar
    NewNow
    New Member
    2 years ago

    I forgot to mention that this scenario it's just for 1 sku (1 SKU with multiple Inventory storage point and with multiple Customer Demand). 

    In the Scenario i'm trying to solve i have multiple SKU, each one with with multiple Inventory storage point and with multiple Customer Demand

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi NewNow ,

    I create two tables as you mentioned.

    Then I create a table and try to use three calculated columns.

    value = 
var _maxindex=
MAXX(
    FILTER(ALL('Output'),'Output'[Inventory Name]=EARLIER('Output'[Inventory Name])),[Index])
RETURN
IF(
    _maxindex<>'Output'[Index],
SUMX(
    FILTER(ALL('Table2'),'Table2'[Customer Name]=EARLIER('Output'[Customer Name])),[Damand QTY]),
SUMX(
    FILTER(ALL('Table1'),'Table1'[Inventory Name]=EARLIER('Output'[Inventory Name])),'Table1'[Avalaible]))
    Test1 = 
var _minvalue=
MINX(
    FILTER(ALL('Output'),
    'Output'[IF]=1),[Index])
return
IF(
    [Index]<_minvalue,
    [value] - SUMX(FILTER(ALL('Output'),'Output'[Index]=EARLIER('Output'[Index])-1),[value]),[value])

    Can you tell me any logic about how to change the DAX codes? I will also do a further research about this topic.

     

     

     

    Best Regards,

    Clara Gong

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

    • NewNow's avatar
      NewNow
      New Member
      2 years ago

      Hello Anonymous ,

      is it possibile to have a solution without the use of the EARLIER function ? Dax guide recomend not to use that one since the introduction of Variables.

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi NewNow ,

         

        Of course, it is possible to improve the column definitions for calculations by using variables instead of EARLIER functions.


        For example

        Subcategory Sales Rank =
COUNTROWS(
FILTER(
Subcategory,
EARLIER(Subcategory[Subcategory Sales]) < Subcategory[Subcategory Sales]
)
) + 1

         

        The CurrentSubcategorySales variable stores the Subcategory Sales column value in the current row context, and the RETURN expression uses it in the modified filter context.

        Subcategory Sales Rank =
VAR CurrentSubcategorySales = Subcategory[Subcategory Sales]
RETURN
COUNTROWS(
FILTER(
Subcategory,
CurrentSubcategorySales < Subcategory[Subcategory Sales]
)
) + 1

         

        More details can be found in the documentation:Use variables to improve your DAX formulas - DAX | Microsoft Learn.

         

        Best Regards,

        Clara Gong

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