Forum Discussion

andersbq's avatar
andersbq
Frequent Visitor
5 years ago
Solved

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 [email protected] Yes 0 Yes 2020-0...
Need Help
  • mahoneypat's avatar
    mahoneypat
    5 years ago

    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

     

     

v-alq-msft's avatar
v-alq-msft
Community Support
5 years ago

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.

 

 

  • andersbq's avatar
    andersbq
    Frequent Visitor
    5 years ago

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

     

     

    • v-alq-msft's avatar
      v-alq-msft
      Community Support
      5 years ago

      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.

      • andersbq's avatar
        andersbq
        Frequent Visitor
        5 years ago

        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.