How to add condicional date in a table?

[Anonymous]

Hi,

I have a sales table with 2 main columns:

Price - How much was sold the product

Installments - How many installments was splitted the payment (in months)

I'm trying to build a third column with the information of how much cash will get in over this year. For instance, if someone bought a product for $400 on november and splitted the payment in 4 times, this year I'll get $100 and next year I'll get the other $300, so the column should return "$100". How can I build that?

[Syndicate_Admin]

Hello @mpoppes,

I agree with @mahoneypat and would go for a DAX measure here, but since you posted the question in PQ forum this is an alternative approach using the Editor and considering you had sale dates in different years.

= Table.AddColumn(#"Changed Type", "Current Year's Receipts", each ([Price]/[Monthly Installments])*List.Count(List.Select(List.Transform({0..[Monthly Installments]-1}, (x) => Date.AddMonths([Date],x)),each Date.IsInCurrentYear(_))),Currency.Type)

[Payeras_BI]

Hello @Anonymous,

I agree with @mahoneypat and would go for a DAX measure here, but since you posted the question in PQ forum this is an alternative approach using the Editor and considering you had sale dates in different years.

= Table.AddColumn(#"Changed Type", "Current Year's Receipts", each ([Price]/[Monthly Installments])*List.Count(List.Select(List.Transform({0..[Monthly Installments]-1}, (x) => Date.AddMonths([Date],x)),each Date.IsInCurrentYear(_))),Currency.Type)

[Anonymous]

@Payeras_BI @Syndicate_Admin 

Thanks for the concern, I'll think about that.

I tried  the formula you sent, but i got an Expression Error, saying that "Changed Type" wasn't recognized. How can I fix it?

[Payeras_BI]

Hi again @Anonymous,

Please remove the string of code shown below from your "Fórmula de coluna personalizada" Text Box.

Table.AddColumn(#"Changed Type", "Current Year's Receipts", each 

This piece of code will be added by PQ once you click "Ok" with a reference to the immediate previous step in you query instead of #"Changed Type" and the name of your Custom Column instead of "Current Year's receipts".

I hope this helps.

[Anonymous]

@Payeras_BI ,

Sorry, I still don't get it. Was it supossed to me delete the first part, like the print bellow?

I'm not that used to the PQ language 

[Payeras_BI · Accepted solution]

Hi @Anonymous ,

See below the difference between the content in the formula bar and the Custom Column's text box.

I also attach the PBIX file for your reference.

[mahoneypat]

Here is a column expression that should do that.  Replace T2 with your actual table name (fix column names too).  FYI that you could also do this as a measure and avoid storing this extra column.

ValueThisYear =
VAR thisdate = T2[SaleDate]
VAR monthsthisyear =
    DATEDIFF ( thisdate, DATE ( YEAR ( thisdate ), 12, 31 ), MONTH )
RETURN
    T2[SalePrice] * monthsthisyear / T2[Installments]

Pat