How to use userelationship

[sadath]

I have made two columns relationship in a table to one column in another table. But 2nd relationship is showing dotted line (which is not active), like

SALES2015[Date1] ----(related to)---> 'date'[Date]
SALES2015[Date2] ---(related to)----> 'date'[Date]

this relation is made to find total in two different way.

2015SalesSameDay:=
calculate(sum(SALES2015[Sales15]),USERELATIONSHIP(SALES2015[Date1],'date'[Date]),FILTER(SALES2015,SALES2015[DATE]<=[MaxDate]))

2015SalesSameDate:=
calculate(sum(SALES2015[Sales15]),USERELATIONSHIP(SALES2015[Date2],'date'[Date]),FILTER(SALES2015,SALES2015[DATE]<=[MaxDate]))

The 2nd formula not getting correct value, how to use USERELATIONSHIP ?

 

[akice]

syntax of USERELATIONSHIP looks correct. Assuming that 'Date1' and 'Date2' are in fact different dates for each row, then I would dig into the "FILTER(SALES2015,SALES2015[DATE]<=[MaxDate])" part of the formula. What is the code for [MaxDate]? if is "MaxDate := max(date[Date])" then as written the "SALES2015[Date] <= [MaxDate]" will always be true for every row and really doesn't do anything other than iterating Sales2015 needlessly. Are you trying to get Year-to-Date amounts?

 

[sadath]

Hi

Thank you for your reply,

last year database is populated with 365 days sales, but this year database updating every day

So last date sales in This year database i got using MaxDate:=Max(Sales2016[Date])-365 . Assume MaxDate is 20/4/2015

then i need the sum of last year upto 20/4/2015

this formula was working fine without USERELATIONSHIP
2015SalesSameDate:=
calculate(sum(SALES2015[Sales15]),USERELATIONSHIP(SALES2015[Date2],'date'[Date]),FILTER(SALES2015,SALES2015[DATE]<=[MaxDate]))

 

[akice]

Now you are referring to a new table 'Sales2016'. Do you have a separate table for each year sales? What is the difference between 'SALES2015[Date]' and 'SALES2015[Date2]' - meaning why do you have 2 relationship's to the same table? Hard to say which way to go when i am not clear on model organization. But assuming you have a separate calendar table with all dates inclusive of the years in the model i prefer to use the time intelligence functions as they make the code a lot more readable and handle things like shifting the calendar to periods with differing number of days in the period.

2015SalesYTD:= TOTALYTD( SUM(SALES2015[Sales15]) ,Calendar[Date] )

Further assuming all years data are in one fact table then for prior year sales you can do:

2014SalesYTD:= TOTALYTD( SUM(SALES2015[Sales15]),PREVIOUSYEAR (Calendar[Date] ) )

and if you need to use a different relationship for some reason, you can do:

2014SalesYTD Different Relationship:= TOTALYTD( SUM(SALES2015[Sales15]),PREVIOUSYEAR (Calendar[Date] ), USERELATIONSHIP ( Sales2015[Date2], Calendar[Date] )

However if each year's data is in a separate table then will need to change things up a bit but still not hard to do. You can either combine into one table using Power Query, or keep separate and use Dax to calculate intended result. If need to use Dax just do:

2015Sales:= SUM( Sales2015[Sales15] )
2016Sales:= SUM( Sales2016[Sales16] )

then combine into one:

Sales:= [Sales2015] + [Sales2016]

Replace [Sales] in the formula's above to get desired Amount.

 

[akice]

Small addition/correction.

For prior year function you may want to use SAMEPERIODLASTYEAR instead of PREVIOUSYEAR. Diffference is in the dates that are returned. SAMPERIODLASTYEAR shifts calendar back one year, PREVIOUSYEAR returns all dates for last year (all 365 days). So may want to do:

2014SalesYTD:= TOTALYTD( SUM(SALES2015[Sales15]),SAMEPERIODLASTYEAR (Calendar[Date] ) )