Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello
I need to calculate the measure. If the Txn Date is greater than the Invoice Date mark it as "failed" otherwise "passed".
DATA
Table 1
Invoice ID | Invoice Number | Invoice Date |
1 | 12345 | 01-Oct-22 |
2 | 12370 | 03-Oct-22 |
3 | 12395 | 05-Oct-22 |
4 | 12420 | 07-Oct-22 |
5 | 12445 | 09-Oct-22 |
6 | 12470 | 11-Oct-22 |
7 | 12495 | 13-Oct-22 |
8 | 12520 | 15-Oct-22 |
Table 2
Invoice ID | Invoice txn ID | Txn Date |
1 | 22445 | 02-Oct-22 |
2 | 22447 | 04-Oct-22 |
2 | 22449 | 03-Oct-22 |
3 | 87890 | 05-Oct-22 |
4 | 878907 | 07-Oct-22 |
4 | 878909 | 07-Oct-22 |
5 | 12909 | 09-Oct-22 |
6 | 1221331 | 11-Oct-22 |
7 | 13331133 | 13-Oct-22 |
8 | 3234121 | 13-Oct-22 |
Result
Invoice Number | Invoice txn ID | Invoice Date | Txn Date | Status |
12345 | 22445 | 1-Oct-22 | 02-Oct-22 | Failed |
12370 | 22447 | 3-Oct-22 | 04-Oct-22 | Failed |
12370 | 22449 | 3-Oct-22 | 03-Oct-22 | Passed |
12395 | 87890 | 5-Oct-22 | 05-Oct-22 | Passed |
12420 | 878907 | 7-Oct-22 | 07-Oct-22 | Passed |
12420 | 878909 | 7-Oct-22 | 07-Oct-22 | Passed |
12445 | 12909 | 9-Oct-22 | 09-Oct-22 | Passed |
12470 | 1221331 | 11-Oct-22 | 11-Oct-22 | Passed |
12495 | 13331133 | 13-Oct-22 | 13-Oct-22 | Passed |
12520 | 3234121 | 15-Oct-22 | 13-Oct-22 | Passed |
Solved! Go to Solution.
Hi @gauravnarchal ,
According to your description, here are my steps you can follow as a solution.
(1) We can create a measure in Table2.
Measure = MAXX(FILTER(Table1,Table1[Invoice ID]=SELECTEDVALUE(Table2[Invoice ID])),Table1[Invoice Date])
(2)Then we can create a measure in Table1.
Status = IF(SELECTEDVALUE(Table2[Txn Date])>[Measure],"failed","passed")
(3) Filter [Measure] and the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @gauravnarchal ,
According to your description, here are my steps you can follow as a solution.
(1) We can create a measure in Table2.
Measure = MAXX(FILTER(Table1,Table1[Invoice ID]=SELECTEDVALUE(Table2[Invoice ID])),Table1[Invoice Date])
(2)Then we can create a measure in Table1.
Status = IF(SELECTEDVALUE(Table2[Txn Date])>[Measure],"failed","passed")
(3) Filter [Measure] and the result is as follows.
Best Regards,
Neeko Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |