Forum Discussion
USERRELATIONSHIP With SUM and Filter Question
Hello,
I have the following tables:
'Calendar'[Date] 1 to M -> 'Sales'[Posted Date]
My calendar table has a calculated column [Is Current Week] that holds a value of 1 if true if the date falls within the current week period I've defined.
I've been trying to write a DAX expression that will return the total sales using USERELATIONSHIP based on a inactive relationship between Calendar[Date] and Sales[Order Date]. I can get a total with this expression for a cummulative value:
CALCULATE( SUM('Sales'[Amount]), USERELATIONSHIP ( 'Calendar'[Date], 'Sales'[Order Date] ))
However, when I try to filter for only sales for the current week, my expression returns nothing. I've tried a few permutations but always end up with a blank result. Here is my latest attempt:
CALCULATE(CALCULATE( SUM('Sales'[Amount]), USERELATIONSHIP ( 'Calendar'[Date], 'Sale'[Order Date] )), FILTER(All
('Calendar'),'Calendar'[Is Current Week] = 1))
Anyone have any pointers on how or if I can accomplish this? I have reviewed this link:
https://www.sqlbi.com/articles/userelationship-in-calculated-columns/
But can't quite seem to get things working with the filter.
How about this version. It should work:
Measure = CALCULATE ( SUM ( 'Sales'[Amount] ), USERELATIONSHIP ( 'Calendar'[Date], 'Sale'[Order Date] ), 'Calendar'[Is Current Week] = 1 )
3 Replies
- mattbriceSolution Sage
How about this version. It should work:
Measure = CALCULATE ( SUM ( 'Sales'[Amount] ), USERELATIONSHIP ( 'Calendar'[Date], 'Sale'[Order Date] ), 'Calendar'[Is Current Week] = 1 )- Promethes_2Helper II
It actually all boiled down to a misconception that I had transactions in the current week when I did not. Thank you for your reply, I marked your answer as the solution as it does work. I
- mattbriceSolution Sage
Good to hear as that was my next suggestion. I coulnd't see an issue with the measure based on facts given.