Forum Discussion
Stuck in DAX: Create calculated table from date based last values and join sum
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.
Thanks for reply.
https://www.dropbox.com/s/plca87zs8aaufma/PowerBIData.xlsx?dl=0
Each tab has Dim/fact table structure with representational data.
Memorable MemberHere 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..
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
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.