cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
RamiSingh
Regular Visitor

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.


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

View solution in original post

6 REPLIES 6
vivran22
Community Champion
Community Champion

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

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


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

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:

stock.png

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

 

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

 

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.

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. 

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors