Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
powerbignc
Helper I
Helper I

Dax USERELATIONSHIP not working

I put together as a simple example of an issue that I'm having with the UseRelationship dax measure not working.

 

The problem is I have a date table where the date column is the unique key and we have a few more columns off to the side that map "Date" to its respective prior year date so that we can use the “UseRelationship” calculation to look at prior date values for a given date selected. In our dates table there are 2 additional columns that look back one year from the current date. One day called [Minus1Year] and is just subtracting one year from the date therefore there is a one to one relationship between date and minus one year. However, some dates are tagged to the same “date one year ago” value which is a separate column. For example, leap year. In the example I provide you will see that there are a few date rows that are mapped to the same “date one year ago” value and for the “minus one year” those values are unique and different. See highlighted example below. What is interesting is that if I build a dax measure that leverages the "date one year ago" relationship the relationship does not work because it is a many-Many relationship with the detail sales table, however, if there is a one to many relationship then the UseRelationship calculation works. As you can see in the example below I have a column for dates and I have a column for date one year ago which does have repeating values for some dates hence January 4th 2019 shares the same date one year ago with January 5th and January 6th of 2019.

 

The revenue column is simply is a sum of the revenue for the given date. No issue here….

Revenue = SUM(Sales[Amount])

 

  • The "Date one year ago" is calculating the revenue amount but using the relationship between date one year ago from the dates table connecting to the transaction date column of the sales table. This calculation is not working properly and is only returning the revenue value. So you can see date one year ago revenue is equal to the revenue column.
  • The minus one year revenue column seems to be calculating properly since that is a unique field from the dates table.

 

How do I fix the date one year ago revenue measure so that given a specific date it uses the respective date one year ago and returns the revenue value from that time similar to how minus one revenue works today. What I would expect to see is that on the values for January 4th 5th and 6th of 2019 they share the same revenue values since the “date one year ago” is the same for all three. Why isn’t this working and how can I get this to work?

 

Below are a few screenshots to help.

 

Here is the model. As you can see all fields from the dates table tie to the transaction date column of the sales fact table.powerbignc_1-1672950835094.png

 

 

Here is a view of the dates table. As you can see for some of the dates they share the same “date one year ago” value. This is done by design because some dates need to share the same “date one year ago” to account for leap year or other reporting dates.

powerbignc_2-1672950850200.png

 

Here is a quick crosstab view showing by date and their respective “date one year ago” and “minus one year” columns. The values in the “DateOneYear – Revenue” are not correct and are simply equaling the Revenue column. As an example, below you can see for January 4th 5th and 6th of 2019, the "date one year ago" was January 4th 2018….so I would expect to see for each of these three rows in the date one year ago revenue column the value of $13.2 million... unfortunately it's simply equaling the revenue. Why is this not working while the “Minus1Year – Revenue” column is working and how do I fix this?

powerbignc_3-1672950866884.png

 

Here are the formulas for each of the 3 measures above. Hoping this is an easy answer….

 

DateOneYearAgo - Revenue = CALCULATE([Revenue],USERELATIONSHIP(Dates[DateOneYearAgo],Sales[TnxDate]))

Minus1Year - Revenue = CALCULATE([Revenue],USERELATIONSHIP(Dates[Minus1Year],Sales[TnxDate]))

Revenue = SUM(Sales[Amount])

 

 

Here are the expected results which I am not able to see...

powerbignc_4-1672950904347.png

 

Thougths on what I am doing wrong and what is the fix for the "DateOneYearAgo - Revenue" measure?

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@powerbignc , for Time intelligence better to use date table and TI functions

 

example

 

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

 

 

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

2 REPLIES 2
powerbignc
Helper I
Helper I

I had to use:

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

 

Here is the probem, I have another table that filters my Date table and when that table filters the Date table the "SAMEPERIODLASTYEAR" formula does not work... For some reason though it does work with the dateadd('Date'[Date],-1,Year).

 

So if you have a filter table filtering the Date table use dateadd NOT SAMEPERIODLASTYEAR

amitchandak
Super User
Super User

@powerbignc , for Time intelligence better to use date table and TI functions

 

example

 

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))

 

 

Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.

Top Kudoed Authors