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
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])
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.
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.
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?
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...
Thougths on what I am doing wrong and what is the fix for the "DateOneYearAgo - Revenue" measure?
Solved! Go to Solution.
@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
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
@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
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
32 | |
15 | |
14 | |
13 | |
9 |