Forum Discussion
Custom Column in Power Query
I have a measure created in DAX to demonstrate:
Customers who have never purchased, but have purchased within the last two months.
I would like a custom column in Power Query with this rule, it can be a boolean column. Here is the DAX formula used:
8 Replies
I demonstrated the formula in DAX because I thought it would make it easier to understand. But no problem.
I need a column that brings me customers who have never purchased but have purchased in the last two months.
I have the following columns to filter:
[NP Name]
[Invoice Date]
[Document] = "A/R Invoice"it may be that I, in addition to the DAX, have problems with English. I can't understand what you mean by customers who have never purchased but have purchased in the last couple of months.
Se l'intenzione è quella di trovare i clienti che hanno comprato SOLO negli ultimi due mesi ma non hanno comprato mai prima, devi fare un table.group sulla colonna [NP Name], poi controlli se la data minima di ogni gruppo ricade negli ultimi due mesi (vedi l'istruzione sotto.).
Se vuoi lo script completo, posta delle tabelle (anche fittizie) con i dati minimi che serveno per trattare tutte le casistiche che ti ritrovi in relatà.
Date.IsInPreviousNMonths([invoicedate],2))
NewStep=let a=Date.AddDays(DateTime.LocalNow(),-60),b=List.Buffer(List.Accumulate(Table.ToRecords(PrivousStepName),{{},{}},(x,y)=>if y[Documento]="Nota fiscal de saída" and y[Data NF]<a then {List.RemoveItems(x{0},{y[Nome do PN]}),x{1}&{y[Nome do PN]}} else if List.Contains(x{1},y[Nome do PN]) then x else {x{0}&{y[Nome do PN]},x{1}}){0}) in Table.AddColumn(PrivousStepName,"CustomColumn",each List.Contains(b,[Nome do PN]))
I didn't understand what to insert in Previous Step Name:
The previous step's name is Columnas Renomeadas2, so you'd use write #"Columnas Renomeadas2" instead of PreviousStepName.
