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

[RamiSingh]

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.

[vivran22]

Hello @RamiSingh 

Request you to share a sample data(prefebly excel file or sample pbix file, not an image) along with the expected output.

Cheers!
Vivek

Blog: vivran.in/my-blog

Feel free to email me for any BI needs .
Connect on LinkedIn
Follow on Twitter

[RamiSingh]

Thanks for reply.
https://www.dropbox.com/s/plca87zs8aaufma/PowerBIData.xlsx?dl=0

Each tab has Dim/fact table structure with representational data.

[stevedep]

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

[RamiSingh]

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]

Hi,

Thanks,

Lookup value could do the trick;

https://docs.microsoft.com/nl-nl/dax/lookupvalue-function-dax

[stevedep · Accepted solution]

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.