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,
can we have a chance to compare values within related dates?
Table 1 and Table has relation with names table also which is unique.
how can we find out results for dates? for example Result for AAAA on a specific date. (Example is in table 2)
(TABLE1)
Name | Currency | Valid from | Valid to |
AAAA | 1.08 | 05/07/2017 | 31/12/2017 |
AAAA | 1.15 | 01/01/2018 | 01/01/2019 |
AAAA | 1.20 | 02/01/2019 | 01/01/2020 |
BBBB | 1.12 | 05/12/2016 | 31/08/2017 |
BBBB | 1.17 | 01/09/2017 | 01/01/2019 |
BBBB | 1.28 | 02/01/2019 | 01/01/2020 |
(TABLE 2)
Name | Date | Result |
AAAA | 01/05/2019 | 1.20 |
BBBB | 01/08/2019 | 1.28 |
Solved! Go to Solution.
İ have solved my own problem with below calculated column formula.
Column2 = CALCULATE(VALUES(Table1[Currency]),FILTER (
Table1,
Table1[Valid From] <= 'Table 2'[Date] &&
Table1[Valid To] > 'Table 2'[Date] && 'Table 2'[Name]=Table1[Name]
))<div> </div>
İ have solved my own problem with below calculated column formula.
Column2 = CALCULATE(VALUES(Table1[Currency]),FILTER (
Table1,
Table1[Valid From] <= 'Table 2'[Date] &&
Table1[Valid To] > 'Table 2'[Date] && 'Table 2'[Name]=Table1[Name]
))<div> </div>
@selpaqm Well done! That's basically what I was getting at, just got the column names confused. My example given just adds an extra error check to ensure that only 1 value for currency remains after all the filtering you have done, so will 100% ensure that your column evaluates properly, even if data updates cause that measure to no longer result in 1 value for currency, it will return blank rather than error as in your solution.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
You will need to check IF the selected date is <= Valid to date and > Valid from date. If you provide a bit more info on table names of data model or sample file we can be more helpful, but that is the general idea.
@selpaqm I have updated with a specific example for you to try:
Assuming that Table 2 has both the Name and Date as columns, you could simply add a Column to Table 2:
Column =
CALCULATE (
IF (
HASONEVALUE ( Table1[Currency] ),
VALUES ( Table1[Currency] )
),
FILTER (
Table1,
AND (
Table1[Valid From] <= Table2[Date],
Table1[Valid To] > Table2[Date]
)
)
)
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
@AllisonKennedy I have checked the example that you are assuming however table1 has no column named as date. so can you please check my example on below link. regards,
Hi @AllisonKennedy thanks to your reply, you can find the example as below, also is there any possiblity to do it with column?
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 |
---|---|
105 | |
94 | |
75 | |
63 | |
62 |
User | Count |
---|---|
137 | |
105 | |
104 | |
80 | |
63 |