Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Stuck in DAX: Create calculated table from date based last values and join sum

I have StockByDate Fact table which is populated by date. The cols are StockValue,ProductID and Date. I have salesItem Fact table with cols ProductID, Date and Sale Quantity. Bothe table are joined...
calculated table
DAX
MAX date
  • stevedep's avatar
    stevedep
    6 years ago

    This would be your code (on the order-item table) to add a new calculated column;

    Orderdate = LOOKUPVALUE('fact-Order'[Date];'fact-Order'[Order];'Fact-OrderItem'[Order])

     

    Please mark as resolved if this works for you. 

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Many thaks for your solution. I appreciate the pain you took to create and explain the solution. The solution looks good.
May I request a simple question regarding indirect relation please. As you have seen, the Table factorderItem is related to date through FactOrder. As FactOrderItem table does not have Date dimension key, I am not sure how to conect them as you have depicted them in model. The fact table design is freezed so I cannot possible add date reference in base table now.
So, measure,
VAR MaxDate = MAX(DateDimv2[Date] )
won't work for date as it is not related. I may need some way to indirectly reference date column but cannot find out how ?
I tried Related etc. but no success.

  • stevedep's avatar
    stevedep
    Memorable Member
    6 years ago

    This would be your code (on the order-item table) to add a new calculated column;

    Orderdate = LOOKUPVALUE('fact-Order'[Date];'fact-Order'[Order];'Fact-OrderItem'[Order])

     

    Please mark as resolved if this works for you.