Forum Discussion
Helper IDate table with multi relationships
I am hoping for some help with date tables - i'm new to PowerBI
I have a table, with 4 main date fields - add_date, Won_date, lost_date and latest_date.
I would like to report revenue based on the first three date. I have been using the USERELATIONSHIP function to switch relationships and this seems to be working.
However, the revenue value is the base currency rather than GBP, which is causing me the issue.
I have an exchange rate table, which is looking up the currency and latest_date for the correct ex-rate rate.
However, as they are using the same date table, it's not working, as we can only change the active relationship one it seems. I have got around this introducing a second date table, linking the Latest_date to this date table. However, this doesn't feel like best practice having multi date tables.
Please help!
3 Replies
- parry2k
Super User
gwright15 based on your input, you don't need 2nd date table, you are correct that you can have only one active relationship, it is if you are connecting same table on multiple dates, which you are already doing and using userrelationship function
You should able to have active relationship between your date table and fx rate table, not sure why you are not able to create active relationship between these two tables.
gwright15Thanks for your help.
Does anything look wrong with this code, which would prevent these relationship working?
Exchange Rate = VAR RevisedDate = Filter( All(Date_Ex_Rate[Date]), Date_Ex_Rate[Date] > Max ( Date_Ex_Rate[Date] ) -5 && Date_Ex_Rate[Date] <= Max(Date_Ex_Rate[Date] ) ) RETURN Calculate (Exchange_Rates_GBP[Ex-Rate], LASTNONBLANK(RevisedDate, Exchange_Rates_GBP[Ex-Rate]),USERELATIONSHIP('Date'[Date],Date_Ex-Rate[Date]) )Deal_Value_Add_Time = Calculate(sum(deals[value]),USERELATIONSHIP('Date'[Date],deals[Add_Time_Local]),USERELATIONSHIP(deals[user_id],users[id]),NOT(deals[status]="deleted"))Deal_Value_Add_Time_GBP = Sumx(deals,deals[Deal_Value_Add_Time]/Exchange_Rates_GBP[Exchange Rate])
Thanks again for your help!
