How to compare 02 dates from different tables with +1 day delta in one of the date

[skumar0105]

Hi Team,

I have a software which is used to investigate and record the root cause of errors made in the process or system at my company. There is a ML model developed to assist the investigator to find root cause in a quick time.

The model output is shown in a Power BI dashboard. it is neccassary to view the dashboard on the same day he is doing the entries in software. now to monitor the adherence of the dashboard, i want to create another report where in i just want to make sure that the person doing the entries in software is refereing to the Model output dashboard or not. 

So here i am comparing 02  dates from 02 different tables, i.e. ML Model output table and the inbuilt Usage metrics report table.

if i compare both dates from both tables, then the problem i am facing is that the inbuilt Usage Metrics report table is refreshed on next day, hence i am not able to get accurate and exact results from the comparison.

i have created 02 measures for this:

Measure = IF(SELECTEDVALUE(Comp_closed_dev[Date_Of_Initiation])=SELECTEDVALUE(User_Plant_Mapping[Date]),TRUE(),FALSE())

Adherence = IF([Measure]=TRUE(),0,1)

but it is not correct.

hence i need a DAX query to add +1 day delta in my Usage Metrics report table i.e. User_Plant_Mapping[Date].

can anyone help me how to add delta in my query.

Any other approach/idea to monitor the adherence would be most welcome.

Thanks in advance.

Satish

[skumar0105]

Hi @lbendlin 

Please find below sample data and expected output:

Table 1

Error ID	Location	Date of Initiation
11	A1	01.10.2022
22	A2	30.08.2022
33	A3	26.08.2022
44	A2	25.08.2022
55	A3	23.08.2022
66	A1	20.08.2022
77	A4	15.08.2022
88	A5	19.08.2022
99	A1	20.08.2022

Table 2:

Dashboard report View	Location	Date of View
2	A1	02.10.2022
3	A2	01.10.2022
1	A3	27.08.2022
4	A2	26.08.2022
2	A3	24.08.2022
3	A1	21.08.2022
1	A4	20.08.2022
3	A5	14.08.2022
2	A1	22.08.2022

Expected result:

Location	% Adherence	Remarks for understanding purpose only
A1	1	next day view, hence should return 1 as a match
A2	1	next day view, hence should return 1 as a match
A3	1	next day view, hence should return 1 as a match
A4	0	05 days gap in view, hence should return 0 as a match
A5	0	No match in date, hence should return 0
A1	0	02 days gap in view hence should return 0

Please let me know if this makes sense for you.

Regards

Satish

[lbendlin]

How do you correlate the dashboard view event to the error ID?  From our sample data it is not clear how a dashboard view event should be attributed to the error instance.

[skumar0105]

i am comparing both tables based on the Location column.

[lbendlin]

That is not good enough, it leads to ambiguity, especially for A1.

[lbendlin]

Please provide sanitized sample data that fully covers your issue.
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523