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 to Dimension Product and Date.

StockBydate[DateKey]=>Date[Key],StockBydate[ProductKey]=>Product[Key]
SalesItem[ProductKey]=>Product[Key], SalesItem[OrderKey]=>SalesOrder[Key],SalesOrder[datekey]=Date[Key]
So, Stock is related to date and product. SalesItem is related to Product and Date via Order.

Now I want to create a calulated table or to show on report
A. Latest Stock Value by Date for Each product
B. Sales Against the product till date -30 days.
*****************************************

ProductName, StockValue,Sales

********************************************
I can create a view in database as I am RDBMS guy, but looking for options in DAX. 
Any Help is greatly appreciated.


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. 

6 Replies

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