Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
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:
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..
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
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.
Hi,
Thanks,
Lookup value could do the trick;
https://docs.microsoft.com/nl-nl/dax/lookupvalue-function-dax
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
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.
Proud to be a Super User!
Awesome Keyboard Shortcusts in Power BI, thumbs up if you like the article
My Community Blog Articles (check them out!)
My Blog - Power M code to automatically detect column types -
How to create test data using DAX!
User | Count |
---|---|
53 | |
28 | |
19 | |
18 | |
14 |
User | Count |
---|---|
92 | |
86 | |
39 | |
23 | |
22 |