cancel
Showing results for
Did you mean:
Frequent Visitor

## Custom column - IF statements within date interval

Hello everyone! I have an ecommerce order table with data that looks like below:

 Date Email Homeloan Sales value New column (converted) 2020-01-01 john@doe.com Yes 0 Yes 2020-01-20 john@doe.com No 150 2020-01-01 jane@doe.com Yes 0 No 2020-05-20 jane@doe.com No 150

I want to add a custom column (marked in red) in power query that calculates if a "Homeloan" is converted or not.

Conversion rule: A homeloan is converted if a purchase (sales value >0) is made within 30 days from homeloan-order (email can be used as common denominator). As per above, first homeloan is converted (purchase made within a month), second one is not.

1 ACCEPTED SOLUTION
Super User IV

Is there a reason this needs to be done in the query editor?  If not, you can do it as a calculated column (or a measure).  Here is a calculated column expression that returns your desired result.

Converted =
VAR thisdate = HomeSales[Date  ]
VAR saledate =
CALCULATE (
MIN ( HomeSales[Date  ] ),
ALLEXCEPT (
HomeSales,
HomeSales[Email]
),
HomeSales[Date  ] > thisdate,
HomeSales[Sales value] > 0
)
VAR daysbetween =
DATEDIFF (
thisdate,
saledate,
DAY
)
RETURN
IF (
HomeSales[Homeloan] = "Yes",
IF (
daysbetween <= 30,
"Yes",
"No"
)
)

Regards,

Pat

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

8 REPLIES 8
Super User IV

Is there a reason this needs to be done in the query editor?  If not, you can do it as a calculated column (or a measure).  Here is a calculated column expression that returns your desired result.

Converted =
VAR thisdate = HomeSales[Date  ]
VAR saledate =
CALCULATE (
MIN ( HomeSales[Date  ] ),
ALLEXCEPT (
HomeSales,
HomeSales[Email]
),
HomeSales[Date  ] > thisdate,
HomeSales[Sales value] > 0
)
VAR daysbetween =
DATEDIFF (
thisdate,
saledate,
DAY
)
RETURN
IF (
HomeSales[Homeloan] = "Yes",
IF (
daysbetween <= 30,
"Yes",
"No"
)
)

Regards,

Pat

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

Frequent Visitor

Thanks @mahoneypat. Reason why I wanted it done in query editor is because I put together the data table there through merging and grouping data. However maybe it's easier to create this column as a DAX formula. Your formula works really well! Although for all the rows where homeloan=Yes and no actual purchase was made in the future (i.e. no record of that email adress in the future), the formula also counts it as a conversion. I added this which seems to work:

IF (
AND(
daysbetween <= 30, daysbetween > 0),

Thanks again!
Community Support

Hi, @andersbq

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a custom column with the following m codes.

let
email=[Email],
date=Table.Max(
Table.SelectRows(#"Changed Type",each [Email]=email and [Sales value]>0),
"Date"
)[Date],
diff=Duration.Days(date-[Date])
in
if [Homeloan]="Yes"
then if diff<=30
then "Yes"
else "No"
else null

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

hey @v-alq-msft! thank you so much for this. It works pretty good, but let's assume there is an email with only a homeloan-order (see printscreen below). The formula then gives me an error. In this scenario it should count as non converted, i.e. "No".

Community Support

Hi, @andersbq

You may try creating a custom column with the following codes. The pbix file is attached in the end.

let
email=[Email],
tab=Table.Max(
Table.SelectRows(#"Changed Type",each [Email]=email and [Sales value]>0),
"Date"
)
in
if tab is null
then "No"
else
if [Homeloan]="Yes"
then if Duration.Days(tab[Date]-[Date])<=30
then "Yes"
else "No"
else null

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Frequent Visitor

@v-alq-msft works well! Many thanks! However when trying to close&apply, it loads for hours without finishing..Maybe too much data for this type of calculation. The dax formula @mahoneypat suggested is possibly the way to go.

Super User III

Paste this M code in a blank query to see the steps. It could be just done in one step but I've split it in more so that it is easier to follow. You could merge all the steps in one if needed.

let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjIwMtA1MAQiJR2lrPyMPIeU/FS95PxcIDcytRhIGijF6iDUGRlgqvPLBxKGpqgKIQYm5qXiNdAUaiCqOoSBsQA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Date = _t, Email = _t, Homeloan = _t, #"Sales value" = _t]),
#"Changed Type" = Table.TransformColumnTypes(Source,{{"Date", type date}, {"Sales value", type number}}),
#"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Table.SelectRows(#"Changed Type",(inner)=> inner[Email] = [Email])[Date]),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom2",{"Custom", "Custom.1"})
in
#"Removed Columns"

Please mark the question solved when done and consider giving kudos if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers

Frequent Visitor

Thank you @AlB - I tried to apply this to my data set but gave up since it was loading/processing for an hour.

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!