Forum Discussion
Lookup table based on other table
Hi, MYDATASTORY , I tried both TREATAS and USERELATIONSHIP and both seem to work in your case; but the USERELATIONSHIP one is a bit tricky. You may refer to the attached file for more details.
Nice weekend!
Hi, MYDATASTORY , as info is limited, what I can do is to make a best guess and a possible solution is to resort to TREATAS func to establish a virtual relationship dedicated to such a lookup. The measure is like this,
FY Lookup =
CALCULATE (
MAX ( 'Data Table'[FY] ),
TREATAS ( VALUES ( Sales[Date] ), 'Data Table'[Date] )
)
Date column comes from Sales table.
CNENFRNL Hi, Thanks for that. I have used this, is working partially, is not matching some of the dates. I am only getting one FY which is 19-20
For the below date, I would expect
05/06/2020 ==>FY20-21
MYDATASTORY If you attach a file with a bit more mockup data, especially a complete structure of data model, maybe I or other gurus here could come up with a better solution.
Apologies not been clear I have added the data, I have already calendar but the problem on my sales date table has two dates column, one I have connected with first date column then match the financial year but the second dates columns which are sales date can not be connected to date table as this is not allowed on Power BI, I have tried LookValue and User Relationship to get the second connection and this not working, here my dummy data
Calendar FY lookup table start April
Date FY 01/04/2020 FY 20-21 02/04/2020 FY 20-21 03/04/2020 FY 20-21 04/04/2020 FY 20-21 05/04/2020 FY 20-21 06/04/2020 FY 20-21 07/04/2020 FY 20-21 08/04/2020 FY 20-21 09/04/2020 FY 20-21 10/04/2020 FY 20-21 11/04/2020 FY 20-21 12/04/2020 FY 20-21 13/04/2020 FY 20-21 01/04/1970 FY 70-71 02/04/1970 FY 70-71 03/04/1970 FY 70-71 04/04/1970 FY 70-71 05/04/1970 FY 70-71 06/04/1970 FY 70-71 07/04/1970 FY 70-71 08/04/1970 FY 70-71 09/04/1970 FY 70-71 10/04/1970 FY 70-71 11/04/1970 FY 70-71 Dummy Sales Date which we need to match FY on sales dates columns dates as we have connected the Purchase date column with the Calendar Date column
Sales ID Purchase Date Sales Dates Amount FY?? 1 01/04/2020 02/04/2020 $ 10.00 2 01/04/2020 03/04/2020 $ 11.00 3 02/04/2020 04/04/2020 $ 12.00 4 03/04/2020 05/04/2020 $ 13.00 5 04/04/2020 06/04/2020 $ 14.00 6 05/04/2020 07/04/2020 $ 15.00 7 06/04/2020 08/04/2020 $ 16.00 8 07/04/2020 09/04/2020 $ 17.00 9 08/04/2020 10/04/2020 $ 18.00 10 09/04/2020 11/04/2020 $ 19.00 11 10/04/2020 12/04/2020 $ 20.00 12 11/04/2020 13/04/2020 $ 21.00 13 12/04/2020 14/04/2020 $ 22.00 14 13/04/2020 15/04/2020 $ 23.00 15 01/04/1970 16/04/2020 $ 24.00 16 02/04/1970 10/04/1970 $ 25.00 17 03/04/1970 11/04/1970 $ 26.00 18 04/04/1970 12/04/1970 $ 27.00 19 05/04/1970 13/04/1970 $ 28.00 20 06/04/1970 14/04/1970 $ 29.00 21 07/04/1970 15/04/1970 $ 30.00 22 08/04/1970 16/04/1970 $ 31.00 23 09/04/1970 17/04/1970 $ 32.00 24 10/04/1970 18/04/1970 $ 33.00 25 11/04/1970 19/04/1970 $ 34.00 26 13/04/2020 20/04/1970 $ 35.00 27 01/04/1970 21/04/1970 $ 36.00 28 02/04/1970 22/04/1970 $ 37.00 29 03/04/1970 23/04/1970 $ 38.00 30 04/04/1970 24/04/1970 $ 39.00 31 05/04/1970 25/04/1970 $ 40.00 32 06/04/1970 26/04/1970 $ 41.00 33 07/04/1970 27/04/1970 $ 42.00 34 08/04/1970 28/04/1970 $ 43.00 35 09/04/1970 29/04/1970 $ 44.00 36 10/04/1970 30/04/1970 $ 45.00 37 11/04/1970 01/05/1970 $ 46.00
