Forum Discussion
Index Match through Power Query or DAX
I hope there is someone who ran into this and has found a solution.
I am importing a table in PowerBI where I have several columns with: Name, Invoice Number, Code, Invoice Date, Invoice Due Date. The issue I have is that on some rows the Invoice Due Date is blank, when it should match the Invoice Due Date from other rows, as long as it is the same Invoice Number. This blank Invoice Due Date happens only when the Code is F or T, when it is a P there will always be a date. I need the Invoice Due Date populated to match based on the Invoice Number and Code = P (there can be several rows with these two criteria, not just one, but the due date will always be the same).
I don't want to use Group By because I need the table in this format, I have many more columns that I don't want to group, there are other invoice details tracked by each row which cannot be aggregated and I need for other reasons. I hope there is a way to create a calculated column and find the corresponding Invoice Due Date based on a matching Vendor Invoice Number and Trans Code being P.
Thanks so much!
No Name Invoice Number Invoice Date CodeInvoice Due DateAging
|
Oana , try
new column =
var _max = maxx(filter(Table, [Invoice Number] = earlier([Invoice Number]) ), [Invoice Due Date])
return
if( isblank([Invoice Due Date]), _max, [Invoice Due Date])
4 Replies
Oana , try
new column =
var _max = maxx(filter(Table, [Invoice Number] = earlier([Invoice Number]) ), [Invoice Due Date])
return
if( isblank([Invoice Due Date]), _max, [Invoice Due Date])This was great, it worked perfectly, it filled in all blank Invoice Due Date with a date! Keep on the great work amitchandak !!!
Due Date Aging CC =
VAR currentinvoicenumber = Data[Invoice Number]
VAR duedate =
MAXX (
FILTER ( Data, Data[Invoice Number] = currentinvoicenumber ),
Data[Invoice Due Date]
)
RETURN
INT ( TODAY () - duedate )Hi Jihwan, thank you for your DAX solution, it really close to what I was looking for, but it calculated Aging based on TODAY(), which is a different interval. I was looking for the same Invoice Due Date to be copied over in the blanks based on Invoice Number. The solution below worked, I do appreciate it though!
