Forum Discussion
Loop check in power Bi
Hi all,
I want to find result on basis on date marked in red, means I'm trying to check date(red one) falls between which group (valid from & expiry date) in data table and get base rate. Already make a unique with col. A, B & C of data table and query table.
For above query 02-01-2018 fall in between (01-06-2017 to 31-12-2018) last row of data table and result = USD 515.00.
Can anybody help me how to get desired result.
Thanks in advance.
2 Replies
- Phil_Seamark
Microsoft Employee
HI Anonymous
This calculated measure will show one way to get the base rate
Base Rate = VAR myDate = DATE(2018,2,1) RETURN MINX( FILTER( ALL('Table1'), 'Table1'[Valid From] <= myDate && myDate < 'Table1'[Expire date] ),'Table1'[Base Rate])Hi Phil_Seamark
Thanks for quick reply, but there are different dates in query table . I made a unique column in starting, so formula will take first value from unique col.(Query table) then look in data table and found matching result then it will check for date, under which pair it will fall and bring the reference base rate.
For. ex. - formula take (5HCMKAN10H) from unique col. of query table, then it will check in data table where it find 2 matching rows (marked with red) and then it will take date(02-01-2018) from query table and check in 2 rows, where it fall in between .So, here (02-01-2018) fall in between(valid from & Expire Date) of last row. so result will be USD 515. Same way for other unique values.
