Measure using USERELATIONSHIP Combined with Filter?

[PowerUser123]

So my data is structured as follows: 

I have a traffic table that is by person and the date they visited. I have another column for the date that person first visited called OrginationDate. Finally I have an ID column which is how the traffic table is connected to the below sales table.

Then I have a sales table that shows sales by person and date.

The sales table is connected to my calendar table by date. I have an indirect connection connecting the traffic table's origination date to the calendar table as well.

From the data above a measure such as: 

TotalSales = SUM('Sales'[Sales])

produces:

A measure to show sales by origination using userelationship would be:

Sales by Origination = CALCULATE(SUM('Sales'[Sales]),USERELATIONSHIP('Traffic'[OriginationDate],Calendar[Date]))

produces:

I ultimately would like to show the below table:

I'm using a matrix visual with the year from my calendar table as the rows. 

The columns in the above visual would be measures for each year (2021 and 2022). I can't figure out how to do the measure. For 2022, I would want to essentially take the TotalSales measure, but filter it for each year someone originated in. I know how to get to that value using userelationship, but I don't know how to combine the two together in one measure. 

[v-rongtiep-msft · Accepted solution]

Hi @PowerUser123 ,

Please refer to my pbix file to see if it helps you.

Create 2 year columnsfor the 2 table.

year = YEAR('origination table'[date])

year_ = YEAR('sales Table'[date])

Then create 2 measures.

firstresult =
VAR _year =
    YEAR ( MAX ( 'origination table'[Orginationdate] ) )
RETURN
    CALCULATE (
        SUM ( 'sales Table'[sales] ),
        FILTER (
            ALL ( 'origination table' ),
            'origination table'[year] = SELECTEDVALUE ( 'origination table'[year] )
                && 'origination table'[person] = SELECTEDVALUE ( 'origination table'[person] )
        )
    )

modified =
VAR _b =
    SUMMARIZE (
        'origination table',
        'origination table'[Orginationdate],
        "aaa", [firstresult]
    )
RETURN
    IF (
        ISINSCOPE ( 'origination table'[Orginationdate] ),
        [firstresult],
        SUMX ( _b, [aaa] )
    )

Then put the 2 year columns into the matrix visual.

If I have misunderstand your meaning, please provide more details.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.