USERELATIONSHIP with Filters

[efstel]

I currently am using the following with an active relationship between my Date Table and the OpportunitiesView. (The Date Table is connected to my SortMonth Table in order to keep the months in chronologic order.) I'm calculating the monthly running totals for each year.   

ClosedWon running total in Month 2024 =

CALCULATE(

    SUM(OpportunitiesView[ClosedWon]),

    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

        )

    )

I want to USERELATIONSHIP between ('Calendar'[Date], OpportunitiesView[CloseDate]),

I believe it needs to go after the SUM function and before the first FILTER function, but that isn’t working. 

What am I doing wrong?

Thanks!

[v-nuoc-msft · Accepted solution]

 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]

I can confirm the above. It is working now. For whatever reason, the numbers were not matching up with my raw data. May have been user error. I may not have updated my dataset when I was checking them. 

[bhanu_gautam]

@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
)
)

[efstel]

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.