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

     

     

mahoneypat's avatar
mahoneypat
Microsoft Employee
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

 

 

andersbq's avatar
andersbq
Frequent Visitor
5 years ago

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!