Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
andersbq
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
mahoneypat
Employee
Employee

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


View solution in original post

8 REPLIES 8
mahoneypat
Employee
Employee

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


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!
v-alq-msft
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.

 

 

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

 

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.

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

AlB
Super User
Super User

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors