Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
I'm trying to create a measure to get Sales Amount w.r.t Delivered Date where CalenderYear is 2007 but having trouble with same.
For Sales Amount w.r.t Delivered Date works well and show amount for Delivered Date CY 2007 with UserRelationship, but when i try to filter only for 2007 its show blank rows .
I'm using ContosoDw for reference and below is code of two measure.
Sales w.r.t Delivered Date
Delivered Amount = CALCULATE ( [SalesAmount], USERELATIONSHIP ( Sales[DeliveryDateKey], 'Date'[DateKey] ) )
Sales w.r.t to DeliveredDate for Year 2007
Delivered Amount in 2007 = CALCULATE ( [Sales Amount], FILTER ( CALCULATETABLE ( Sales, USERELATIONSHIP( Sales[DeliveryDateKey], 'Date'[DateKey] ) ), RELATED ( 'Date'[Calendar Year Number] ) = 2007 ) )
Solved! Go to Solution.
Hello,
Try this (with the correct table and column names from your model) ;
Hello,
Try this (with the correct table and column names from your model) ;
Hi Adrian,
Its showing below error (USERRELATIONSHIP function can only use the two columns references participating in relationship), while using your concept. I've replaced SUM(Sales[Amount]) with measure already build.
Which is using an below code as an expression
SUMX(Sales,Sales[Quantity]*Sales[Unit Price])
Yeah it worked, and last error was due to wrong column used for relationship.
The only thing is ,its only showing for CY2007 and not repeating for all CY.
This will show the delivered amount in 2017 on all year lines;
yeah @avanderschilden changing your first expression from
CALCULATE(
[SalesAmount],// its a measure with expression SUMX(Sales,Sales[Quantity]*Sales[Unit Price])
USERELATIONSHIP('Date'[DateKey],Sales[DeliveryDateKey]),
YEAR('Date'[Date])=2007 // Changed from YEAR(Sales[DeliveryDate])=2007 allowed 2007 sales w.r.t to delivered date on all CY
)
Thanks for your guidance, much appreciated
Regards,
Harry
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
25 | |
20 | |
20 | |
14 | |
13 |
User | Count |
---|---|
43 | |
37 | |
25 | |
24 | |
22 |