cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
visittokiran
Frequent Visitor

DATE AND TIME MATCHING WITH ERROR

I have following two tables with date & time table 

I want to fetch data in table A as shown below

 TABLE B  
CODEDATE & TIMEINCIDENT IDENTRY TYPE
H1234501/10/2021 08:0075769338A
H7679302/10/2021 08:0079879893B
H8797403/10/2021 08:00987979721 C
H8944804/10/2021 08:003628643E

 

TABLE A 
CODEDATE & TIME
H1234501/10/2021 08:15
H7679302/10/2021 08:30
H8797403/10/2021 08:20
H8944804/10/2021 08:05

Final result required 

TABLE A (FINAL)  DATA MATCHING WITH ERROR OF 30 MINS
CODEDATE & TIMEincidentENTRY TYPE
H1234501/10/2021 08:1575769338A
H7679302/10/2021 08:3079879893B
H8797403/10/2021 08:20987979721 C
H8944804/10/2021 08:053628643E
1 ACCEPTED SOLUTION

@visittokiran  you can create a measure like this and utilize this a visual level filter

 

_filter = 
VAR _a =CALCULATE(MAX('table A'[DATE & TIME]))
VAR _b =CALCULATE(MAX('table B'[DATE & TIME]))
VAR _c = ABS(DATEDIFF(_a,_b,MINUTE))
RETURN switch(true(),_c=30||_c=0,1)




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


View solution in original post

6 REPLIES 6
visittokiran
Frequent Visitor

@BA_Pete  in both data tables there are multiple entried for same code ... 

BA_Pete
Super User
Super User

Hi @visittokiran ,

 

I'm not sure I fully understand your requirements here.

It looks like [CODE] is a unique field in both tables, so why not just merge tableA with tableB on tableA[CODE] = tableB[CODE]?

 

Pete

Sorry for late reply @BA_Pete , you are right but sorry i did not mentioned that in that data there are multiple entried in a days for same code. In that case how to match data ? 

@visittokiran ,

 

OK, no worries.

 

Can you provide a more representative example of your data please?

If you can create an example of tableA and tableB in Power Query (Enter Data) then just post the M code for each that would be ideal.

 

Pete

@BA_Pete  I will try to explain in detail.

There are two data tables table A & Table B

I want to fetch data from table B to table A  or Creating dashboard will also be fine.

While fetching data from Table B following logic need to be implemented.

First for every row in Table A it first Code no will be checked.

IF code No Matched then Date will be mate if Date Matched then 

Time will be matched But time is not exact match in both table.

There is some time gap in time mentioned in table A.

So I want to match time with +-30 min span.

If time also matches then data from INCIDENT & ENTRY TYPE SHALL BE FETCHED IN TABLE A FOR THAT SPECIFIC ROW IN TABLE A.

@visittokiran  you can create a measure like this and utilize this a visual level filter

 

_filter = 
VAR _a =CALCULATE(MAX('table A'[DATE & TIME]))
VAR _b =CALCULATE(MAX('table B'[DATE & TIME]))
VAR _c = ABS(DATEDIFF(_a,_b,MINUTE))
RETURN switch(true(),_c=30||_c=0,1)




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!






New Animated Dashboard: Sales Calendar


View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.

Top Kudoed Authors