cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Custom column - IF statements within date interval

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

 

Date  EmailHomeloanSales valueNew column (converted)
2020-01-01john@doe.comYes0Yes
2020-01-20john@doe.comNo150 
2020-01-01jane@doe.comYes0No
2020-05-20jane@doe.comNo150 

 

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.

 

Thanks in advance!!

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User III
Super User III

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.

 

mahoneypat_0-1600738115874.png

 

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? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




View solution in original post

8 REPLIES 8
Highlighted
Super User II
Super User II

Hi @andersbq 

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]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each if Duration.Days(List.Max([Custom])-List.Min([Custom]))>30 then "Yes" else "No"),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Converted", each if List.Min([Custom]) = [Date] then [Custom.1] else ""),
    #"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 

 

SU18_powerbi_badge

Highlighted
Community Support
Community Support

Hi, @andersbq 

 

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

Table:

i1.png

 

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:

i2.png

 

Best Regards

Allan

 

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

 

 

Highlighted

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".

 

andersbq_0-1600694540766.png

andersbq_1-1600694683824.png

 

Highlighted
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. 

Highlighted
Super User III
Super User III

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.

 

mahoneypat_0-1600738115874.png

 

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? Please mark my post as a solution! Kudos are also appreciated!

Proud to be a Super User!




View solution in original post

Highlighted

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:

b1.png

 

Best Regards

Allan

 

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

Highlighted

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!
Highlighted

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

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Community Conference

Microsoft Power Platform Communities

Check out the Winners!

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors