Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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.
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.