Forum Discussion
USERELATIONSHIP with Filters
Hi efstel
Allow me to confirm a few questions for you:
1. Relationship Activation: Ensure that the relationship between Calendar[Date] and OpportunitiesView[CloseDate] is correctly defined and inactive by default.2. Data Context: Verify that the data in OpportunitiesView and Calendar tables are correctly populated and that there are no missing or incorrect dates.
3. SortMonth Table: Confirm that the SortMonth table is correctly set up and that the Sort and Month columns are properly defined.
By adding USERELATIONSHIP('Calendar'[Date], OpportunitiesView[CloseDate]) within the CALCULATE function, it should correctly activate the relationship for your calculation.
After the above questions are confirmed, bhanu_gautam's reply should be effective.
If you still have problems, it is best to provide the pbix file and be careful to delete sensitive data.
Regards,
Nono Chen
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
efstel , Try using below DAX
ClosedWon running total in Month 2024 =
CALCULATE(
SUM(OpportunitiesView[ClosedWon]),
USERELATIONSHIP('Calendar'[Date], OpportunitiesView[CloseDate]),
FILTER(
CALCULATETABLE(
SUMMARIZE('SortMonth', 'SortMonth'[Sort], 'SortMonth'[Month]),
ALLSELECTED('SortMonth')
),
ISONORAFTER(
'SortMonth'[Sort], MAX('SortMonth'[Sort]), DESC,
'SortMonth'[Month], MAX('SortMonth'[Month]), DESC
)
),
FILTER(
ALLSELECTED('Calendar'),
'Calendar'[Date] <= MAX('Calendar'[Date]) && YEAR('Calendar'[Date]) = 2024
)
)
Thanks bhanu_gautam, but if you notice in my request above, that is exactly how I put it in as you have proposed. It is not working properly.