Forum Discussion

vmcode's avatar
vmcode
Frequent Visitor
8 years ago
Solved

loop Statement to DAX

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.

 

storedate of saleamountnumber of paymentpayment amountpayment date
1502-01-2018$65.894

3

$21.96404/01/2018
1802-01-2018$51.246

6

$8.54105/01/2018
2002-01-2018$46.860

3

$15.62005/01/2018
  $164.000   
      
      
     Report
storedate of saleamountpaymentpayment amountpayment date
1502-01-2018$65.8941$21.96404-01-2018
1502-01-2018$65.8942$21.96401-02-2018
1502-01-2018$65.8943$21.96403-03-2018
1802-01-2018$51.246

1

$8.54105-01-2018
1802-01-2018$51.2462$8.54101-02-2018
1802-01-2018$51.2463$8.54103-03-2018
1802-01-2018$51.2464$8.54102-04-2018
1802-01-2018$51.2465$8.54102-05-2018
1802-01-2018$51.2466$8.54101-06-2018
2002-01-2018$46.860

1

$15.62005-01-2018
2002-01-2018$46.860

2

$15.62001-02-2018
2002-01-2018$46.860

3

$15.62003-03-2018
    $163.998 

 

  • Anonymous's avatar
    Anonymous
    8 years ago

    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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      vmcode
      Frequent Visitor
      8 years ago

      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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    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's avatar
      vmcode
      Frequent Visitor
      8 years ago

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