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 An...
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.

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.