cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
JoaoMS
Helper II
Helper II

Look for a date in a Column List

Hi all, I need your help with the following issue. In our company we have a list of fixed dates for paying suppliers (two dates each month, usually the 1st and 15th day of each month). These dates are in a one-column table (header "Payment dates").

Our accountabilty policy is that whenever an invoice is received from a supplier, this invoice is paid in the soonest fixed date for payments. For example, in November we have 2 fixed dates for payment: Nov 4th and Nov 15th, so if a supplier sends its invoice today (Nov 5th), he will be paid on Nov15 th. 

Then, we have a database of all the invoices from different suppliers with one field related to the date in which the invoice was received (header "Invoice date"). What we want to calculate is a new column which shows the payment date for each invoice.

 

Thanks in advance

Joao

1 ACCEPTED SOLUTION

Hi,

In the invoice table write this calculated column formula

=CALCUATE(MIN(PaymentTable[Fixed_Dates]),FILTER(PaymentTable,PaymentTable[Fixed_Dates]>EARLIER(InvoiceTable[Invoice_Dates])))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

7 REPLIES 7
JoaoMS
Helper II
Helper II

Dear all, i'm very sorry for my delay in answering your comments, unfortunately I'm already have this problem. I'm attaching a picture with the two (2) tables which have the main information. The "InvoiceTable" will always have new data, while the "PaymentTable" has fixed dates for the payment days. We expect to have the "Output" table relating the information of the "InvoiceTable" with the day of payment. 

 

Invoice.png

 

Regards,

Joao

Hi,

In the invoice table write this calculated column formula

=CALCUATE(MIN(PaymentTable[Fixed_Dates]),FILTER(PaymentTable,PaymentTable[Fixed_Dates]>EARLIER(InvoiceTable[Invoice_Dates])))

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

FrankAT
Community Champion
Community Champion

Hi @JoaoMS ,

in Power Query Editor follow theses steps:

 

// InvoiceTable
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjE1MFTSUTIw1Dc00DcyUIrVAYsZgcSM9A1NkcSMQWLG+kBhkFgsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Invoice = _t, #"lnvoice Date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Invoice", Int64.Type}, {"lnvoice Date", type text}}),
    #"Changed Type with Locale" = Table.TransformColumnTypes(#"Changed Type", {{"lnvoice Date", type date}}, "en-US"),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type with Locale",{{"lnvoice Date", "Dates"}})
in
    #"Renamed Columns"

// PaymentTable
let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjDUByIjA6VYHTDH0BTOMUKWMUKWMUaWMUaWMUGWMYHJxAIA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [FixedJDates = _t]),
    #"Changed Type with Locale" = Table.TransformColumnTypes(Source, {{"FixedJDates", type date}}, "en-US"),
    #"Renamed Columns" = Table.RenameColumns(#"Changed Type with Locale",{{"FixedJDates", "Dates"}})
in
    #"Renamed Columns"

// Append1
let
    Source = Table.Combine({InvoiceTable, PaymentTable}),
    #"Sorted Rows" = Table.Sort(Source,{{"Dates", Order.Ascending}}),
    #"Removed Top Rows" = Table.Skip(#"Sorted Rows",1),
    #"Filled Down" = Table.FillDown(#"Removed Top Rows",{"Invoice"}),
    #"Grouped Rows" = Table.Group(#"Filled Down", {"Invoice"}, {{"Count", each _, type table [Invoice=number, Dates=date]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "Custom", each Table.FirstN([Count],2)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Skip", each Table.Skip([Custom],1)),
    #"Removed Other Columns" = Table.SelectColumns(#"Added Custom1",{"Skip"}),
    #"Expanded Skip" = Table.ExpandTableColumn(#"Removed Other Columns", "Skip", {"Invoice", "Dates"}, {"Invoice", "Dates"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Expanded Skip",{{"Invoice", Int64.Type}, {"Dates", type date}})
in
    #"Changed Type"

// Merge1
let
    Source = Table.NestedJoin(Append1, {"Invoice"}, InvoiceTable, {"Invoice"}, "InvoiceTable", JoinKind.LeftOuter),
    #"Expanded InvoiceTable" = Table.ExpandTableColumn(Source, "InvoiceTable", {"Dates"}, {"Dates.1"}),
    #"Reordered Columns" = Table.ReorderColumns(#"Expanded InvoiceTable",{"Invoice", "Dates.1", "Dates"}),
    #"Renamed Columns" = Table.RenameColumns(#"Reordered Columns",{{"Dates.1", "InvoiceDate"}, {"Dates", "PaymentDate"}})
in
    #"Renamed Columns"

Regards FrankAT

You need a  column in the Payment table to merge against. Perhaps the Invoice number?

 

Please provide sample data, not screenshots. Paste the data in a table, or link to an Excel file via OneDrive so we don't have to rekey. Screenshots are nice to see expected output, but then you are expecting someone to rekey that info in to do the work.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Hi, please find attached the file in the following link:

 

https://drive.google.com/open?id=1ERzIaT80BPovaLuoYxoAy8xHdhWtK8aw 

 

Regards,

Joao

Ashish_Mathur
Super User
Super User

Hi,

Share both datasets and for a few rows show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
sturlaws
Resident Rockstar
Resident Rockstar

Hi @JoaoMS,

use something like this for you calculated column:

pd =
CALCULATE (
    MIN ( paymentDates[paymentDate] );
    FILTER ( paymentDates; paymentDates > invoice[invoiceDate] )
)

 

Cheers,
Sturla

If this post helps, then please consider Accepting it as the solution. Kudos are nice too.

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!