Lookup table based on other table

[MYDATASTORY]

Hi 

I have a quick question,

I have two separate tables, one table has dates and the other tables have dates and the Finacial year column and I want to do a lookup. I have connected the Purchase dates with the Calendar date, now I need to connect the Sales date with the Calendar Date so I can get the Financial year lookup.

Sales Table with two date columns

Sales ID	Purchase Date	Sales Dates	Amount	Sales Dates (Expected result)
1	01/04/2020	02/04/2020	$    10.00	FY20-21
2	01/04/2020	03/04/2020	$    11.00	FY20-21
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	FY70-71
19	05/04/1970	13/04/1970	$    28.00	FY70-71
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

FY lookup 

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

[CNENFRNL]

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.

[MYDATASTORY]

@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

[CNENFRNL]

@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.

[MYDATASTORY]

@CNENFRNL @amitchandak 

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

[CNENFRNL · Accepted solution]

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!

[amitchandak]

@MYDATASTORY , Ideally you should have a date table with FY to be part of your date table. Not clear what is the issue.

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
1.Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calend...

examples

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
This year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR('Date'[Date]),"12/31"))
Last year Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))