Forum Discussion

PaulBrunclair's avatar
PaulBrunclair
Frequent Visitor
2 years ago
Solved

Special requirements

Hello experts, I have a particular requirement and I've been scratching my head for some time already. Could you kindly help with this one? I want to monitor my stock and see the evolution on what...
Need Help
  • AmiraBedh's avatar
    AmiraBedh
    2 years ago

    I suppose that your current model is one table as you didn't specify how it is designed.

    You'll need to create a calendar table that starts from today's date and goes to the end of the year :

     

     

    CalendarTable = 
ADDCOLUMNS (
    CALENDAR ( TODAY(), DATE(YEAR(TODAY()), 12, 31) ),
    "Year", YEAR([Date]),
    "Month", MONTH([Date]),
    "Day", DAY([Date]),
    "WeekDay", WEEKDAY([Date], 2)
)

     

     

     

    Then create a calculated table for the stock calculation :

     

     

    StockCalculation = 
ADDCOLUMNS (
    CALENDAR ( TODAY(), DATE(YEAR(TODAY()), 12, 31) ),
    "Daily Production", 90,  
    "Orders", 
    VAR CurrentDate = [Date]
    RETURN
        SUMX (
            FILTER ( OrdersTable, OrdersTable[Date] = CurrentDate ),
            OrdersTable[Order quantity]
        ),
    "Previous Day Stock",
    VAR PreviousDayStock = 
        CALCULATE (
            SUMX ( StockCalculation, [Final Stock] ),
            FILTER ( StockCalculation, [Date] = CurrentDate - 1 )
        )
    RETURN 
        IF (
            ISBLANK ( PreviousDayStock ), 150, 
            PreviousDayStock
        ),
    "Final Stock",
    [Previous Day Stock] + [Daily Production] - [Orders]
)

     

     

     

    Don't forget that your main table and theStockCalculation  must linked by the Date to the date dimension.

  • PaulBrunclair's avatar
    PaulBrunclair
    2 years ago

    Hello Greg,

    Thank you very much for your quick reply.

    You are quite close to the solution, the problem is that I'm getting some wrong order quantities I don't know why. Please find below your code with my own changes.

     
    Calculation Table =
        VAR __Today = TODAY()
        VAR __CurrentStock = [Total Stock]
        VAR __DailyProduction = 90
        VAR __Table =
            ADDCOLUMNS(
                CALENDAR( __Today, DATE( YEAR( __Today ), 12, 31 ) ),
                "__Production", __DailyProduction,
                "__Orders", VAR __Date = [Date] RETURN MAXX( FILTER( 'Sales vs Stock ACL', [Dates] = __Date), 'Sales vs Stock ACL'[Order Quantity] )
            )
        VAR __Table2 =
            ADDCOLUMNS(
                __Table,
                "__Stock", VAR __Date = [Date] RETURN __CurrentStock + SUMX( FILTER( __Table, [Date] <= __Date ), [__Production] ) - SUMX( FILTER( __Table, [Date] <= __Date ), [__Orders] )
            )
    RETURN
        __Table2

    Thanks a lot for your help,

     

    Paul

AmiraBedh's avatar
AmiraBedh
Icon for Super User rankSuper User
2 years ago

I suppose that your current model is one table as you didn't specify how it is designed.

You'll need to create a calendar table that starts from today's date and goes to the end of the year :

 

 

CalendarTable = 
ADDCOLUMNS (
    CALENDAR ( TODAY(), DATE(YEAR(TODAY()), 12, 31) ),
    "Year", YEAR([Date]),
    "Month", MONTH([Date]),
    "Day", DAY([Date]),
    "WeekDay", WEEKDAY([Date], 2)
)

 

 

 

Then create a calculated table for the stock calculation :

 

 

StockCalculation = 
ADDCOLUMNS (
    CALENDAR ( TODAY(), DATE(YEAR(TODAY()), 12, 31) ),
    "Daily Production", 90,  
    "Orders", 
    VAR CurrentDate = [Date]
    RETURN
        SUMX (
            FILTER ( OrdersTable, OrdersTable[Date] = CurrentDate ),
            OrdersTable[Order quantity]
        ),
    "Previous Day Stock",
    VAR PreviousDayStock = 
        CALCULATE (
            SUMX ( StockCalculation, [Final Stock] ),
            FILTER ( StockCalculation, [Date] = CurrentDate - 1 )
        )
    RETURN 
        IF (
            ISBLANK ( PreviousDayStock ), 150, 
            PreviousDayStock
        ),
    "Final Stock",
    [Previous Day Stock] + [Daily Production] - [Orders]
)

 

 

 

Don't forget that your main table and theStockCalculation  must linked by the Date to the date dimension.

  • PaulBrunclair's avatar
    PaulBrunclair
    Frequent Visitor
    2 years ago

    Hello Amira,

    Thank you very much for your reply.

    My stock is not related to a calender table, the only thing I can do is to calaculate the total amount.

    The reason why I am stuck at this level therefore, I cannot use your solution.

     

    I do apreciate your help and thanks a lot

     

    Paul