loop Statement to DAX

[vmcode]

Hello dear, I am starting with dax and I must implement a report where you can see the amount and date of payment of sales to the credit, I have tried with the function dateadd but I need to iterate through the amount of payment installments.
Since already grateful.

store	date of sale	amount	number of payment	payment amount	payment date
15	02-01-2018	$65.894	3	$21.964	04/01/2018
18	02-01-2018	$51.246	6	$8.541	05/01/2018
20	02-01-2018	$46.860	3	$15.620	05/01/2018
		$164.000
					Report
store	date of sale	amount	payment	payment amount	payment date
15	02-01-2018	$65.894	1	$21.964	04-01-2018
15	02-01-2018	$65.894	2	$21.964	01-02-2018
15	02-01-2018	$65.894	3	$21.964	03-03-2018
18	02-01-2018	$51.246	1	$8.541	05-01-2018
18	02-01-2018	$51.246	2	$8.541	01-02-2018
18	02-01-2018	$51.246	3	$8.541	03-03-2018
18	02-01-2018	$51.246	4	$8.541	02-04-2018
18	02-01-2018	$51.246	5	$8.541	02-05-2018
18	02-01-2018	$51.246	6	$8.541	01-06-2018
20	02-01-2018	$46.860	1	$15.620	05-01-2018
20	02-01-2018	$46.860	2	$15.620	01-02-2018
20	02-01-2018	$46.860	3	$15.620	03-03-2018
				$163.998

[v-yuezhe-msft · Accepted solution]

@vmcode,

You can use Power Query to get the above result. Add two new blank queries in your PBIX file, then paste the following codes  to the Advanced Editor of the blank queries.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMtY31DcyMLRQitUBcYwQHBNkGVM4JxYA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Date = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}})
in
    #"Changed Type"

let
    BufferedHolidays = Table.Buffer(Holidays),
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("Xc1RCsAgDAPQq4jsU6rtTKlnEe9/De2EjfmXQB7pPTJiipwlS2Fb8VKQtbDS7U2YmoZnUvdkpIXsQGAS+Ey9GaFug89IOUxVMn2PGKTyR2MC", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [store = _t, #"date of sale" = _t, amount = _t, #"number of payment" = _t, #"payment amount" = _t, #"payment date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"store", Int64.Type}, {"date of sale", type date}, {"amount", Currency.Type}, {"number of payment", Int64.Type}, {"payment amount", Currency.Type}, {"payment date", type date}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Date.AddMonths([payment date], [number of payment]-1)),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom1", each List.Dates([payment date],Duration.Days(Duration.From([Custom]-[payment date]))+1,#duration(1,0,0,0))),
    #"Expanded Custom1" = Table.ExpandListColumn(#"Added Custom1", "Custom1"),
    #"Added Custom2" = Table.AddColumn(#"Expanded Custom1", "Month", each Date.Month([Custom1])),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "Ifholiday", each if List.Contains ( Table.Column(BufferedHolidays, "Date") ,  [Custom1]) then 0 else 1),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom3", each ([Ifholiday] = 1)),
    #"Grouped Rows" = Table.Group(#"Filtered Rows", {"store", "date of sale", "amount", "number of payment", "payment amount", "payment date", "Month"}, {{"First date", each List.Min([Custom1]), type date}}),
    #"Renamed Columns" = Table.RenameColumns(#"Grouped Rows",{{"Month", "payment"}, {"First date", "new payment date"}})
in
    #"Renamed Columns"

Regards,
Lydia

[vmcode]

Thank you very much Lydia, I will do the tests during the night and I will tell you how it was
regards

[v-yuezhe-msft]

@vmcode,

What logic do you use to determine the payment date(01-02-2018,03-03-2018,etc) in your desired report? And what is the format of your date, dd/mm/yyyy or mm/dd/yyyy?

Regards,
Lydia

[vmcode]

Hello Lydia, thanks for answering.
The payment logic corresponds to 30 days, but considering holidays and the date format here is dd / mm / yyyy.

Regards.