Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Dear All,
I have a table with the following structure:
Date | Invoice | Document | Amount |
02-01-2018 | 1224 | 700 | |
07-01-2018 | 1223 | 1500 | |
05-01-2018 | 1223 | -1000 | |
06-01-2018 | 1223 | -500 | |
08-01-2018 | 1224 | -700 |
As you can see the link between lines is Invoice<->Document
I am looking for a measure or calculated column that gives me the latest Date in which [Invoice]=[Document]
The desired output in the example would be:
Date | Invoice | Document | Amount | Date2 |
02-01-2018 | 1224 | 700 | 08-01-2018 | |
07-01-2018 | 1223 | 1500 | 06-01-2018 | |
05-01-2018 | 1223 | -1000 | ||
06-01-2018 | 1223 | -500 | ||
08-01-2018 | 1224 | -700 |
I've tried different approaches, one of those being:
Solved! Go to Solution.
Hmm... that's weird. I just ran a quick test here and it seems to work fine.
1. Are you sure you've copied the code correctly? It sounds like there might be a misplaced comma or similar that is tricking the FILTER( ) function
2. Are [Document] and [Invoice] of the same type, both numbers or both text? They need to
Additionally, if you want Date2 to appear only in the rows where Document is non-blank, as you show in your example, we need a small modification to what I wrote previously:
Date2 = IF ( NOT ISBLANK ( Table1[Document] ), CALCULATE ( MAX ( Table1[Date] ), FILTER ( Table1, Table1[Invoice] = EARLIER ( Table1[Document] ) ) ) )
Hi @setis
You were close, your approach makes sense, but LOOKUPVALUE requires a column in the first argument. Try this for a calculated column in your table:
Date2 = CALCULATE ( MAX ( Table1[Date] ), FILTER ( Table1, Table1[Invoice] = EARLIER ( Table1[Document] ) ) )
Hi @AlB, thanks for your answer.
This is giving me the error: "DAX comparison operations do not support comparing values of type True/False with values of type Text. Consider using the VALUE or FORMAT function to convert one of the values."
The Error is probably because the column [Document] is formated as text. Some of the cells are text strings. Apologies for not mentioning this before.
Hmm... that's weird. I just ran a quick test here and it seems to work fine.
1. Are you sure you've copied the code correctly? It sounds like there might be a misplaced comma or similar that is tricking the FILTER( ) function
2. Are [Document] and [Invoice] of the same type, both numbers or both text? They need to
Additionally, if you want Date2 to appear only in the rows where Document is non-blank, as you show in your example, we need a small modification to what I wrote previously:
Date2 = IF ( NOT ISBLANK ( Table1[Document] ), CALCULATE ( MAX ( Table1[Date] ), FILTER ( Table1, Table1[Invoice] = EARLIER ( Table1[Document] ) ) ) )
@AlBThank you so much!! You were right. I had misplaced a comma and thanks a lot for the last addition to the code. This is a great learning!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
106 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |