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. 

    • stevedep's avatar
      stevedep
      Icon for Memorable Member rankMemorable Member
      6 years ago

      Here you go:

      - For the latest stock:

      LastestStock = 
var seleteddate = SELECTEDVALUE(DateDimv2[Date])
var lastknowndate = 
CALCULATE (
    LASTNONBLANK (
        DateDimv2[Date];
       CALCULATE(SUM('Fact-DailyStock'[Stock]))
    );
    DateDimv2[Date] < seleteddate
)
return 
CALCULATE(CALCULATE(SUM('Fact-DailyStock'[Stock])); FILTER(ALL(DateDimv2[Date]);DateDimv2[Date]=lastknowndate))

      For the running total:

      sales_RT = 
VAR MaxDate = MAX ( DateDimv2[Date] ) -- Saves the last visible date
VAR DaysBeforeDatea = MaxDate - 5
RETURN
    CALCULATE (
       CALCULATE(SUM('Fact-OrderItem'[Qty]));          -- Computes sales amount
       DateDimv2[Date]<= MaxDate; DateDimv2[Date] >= DaysBeforeDatea;   -- Where date is before the last visible date
        ALL ( DateDimv2 )               -- Removes any other filters from Date
    )

      The explanation for the RT found here. 

      Visual with results:

      Please mind the data model and the use of a date table:

       

      Power BI file available for download here. 

       

      Please mark as solution if this is what you are looking for. Thanks!

       

      p.s. Kudos are appreciated..

       

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