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 

 

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.