Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 |
Solved! Go to 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!
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@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.
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
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 |
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!
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@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))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
101 | |
86 | |
64 |