cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mpoppes
Helper I
Helper I

How to add condicional date in a table?

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?

1 ACCEPTED SOLUTION

Hi @mpoppes ,

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.

Payeras_BI_0-1627630379508.png

 

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

View solution in original post

7 REPLIES 7
Syndicate_Admin
Administrator
Administrator

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.

Payeras_BI_1-1627376682395.png

= 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
Super User I
Super User I

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.

Payeras_BI_1-1627376682395.png

= 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)

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

@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?

mpoppes_1-1627417401752.png

 

 

Hi again @mpoppes,

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.

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

@Payeras_BI ,

 

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

mpoppes_0-1627592603372.png

 

I'm not that used to the PQ language 

Hi @mpoppes ,

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.

Payeras_BI_0-1627630379508.png

 

 

If this post answered your question, please mark it as a solution to help other users find useful content.
Kudos are another nice way to acknowledge those who tried to help you.

J. Payeras
Mallorca, Spain

View solution in original post

mahoneypat
Super User IV
Super User IV

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 ( thisdateDATE ( YEAR ( thisdate )1231 )MONTH )
RETURN
    T2[SalePrice] * monthsthisyear / T2[Installments]

 

Pat

 





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Kudoed Authors