Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
selpaqm
Helper V
Helper V

Compare Data within related dates

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)

NameCurrencyValid fromValid to
AAAA1.0805/07/201731/12/2017
AAAA1.1501/01/201801/01/2019
AAAA1.2002/01/201901/01/2020
BBBB1.1205/12/201631/08/2017
BBBB1.1701/09/201701/01/2019
BBBB1.2802/01/201901/01/2020

 

(TABLE 2)

NameDateResult
AAAA01/05/20191.20
BBBB01/08/20191.28

 

 

1 ACCEPTED SOLUTION
selpaqm
Helper V
Helper V

İ 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>

 

View solution in original post

5 REPLIES 5
selpaqm
Helper V
Helper V

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

 

 


Please @mention me in your reply if you want a response.

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
Super User
Super User

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]
)
)
)


Please @mention me in your reply if you want a response.

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,

 

Example 

Hi @AllisonKennedy thanks to your reply, you can find the example as below, also is there any possiblity to do it with column? 

 

Sample.pbix 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.