IF Statement with dates

[Anonymous]

Hi Community!

I'm am looking to see if somone can help me out with this DAX formula.

The ultimate result I am looking for is, if a last order date is prior to 8/1, the Customer Ordered column would reflect "No". 

You can see in the highilighted area below, my current dax formula isn't giving me the correct result.

My current formula is:

Customer Ordered = 
var Firstdayofmonth = EOMONTH(TODAY(), -1) +1
var Lastorderdate = CALCULATE(MAX(Sales_Invoices[Posting Date]), FILTER(Sales_Invoices,Sales_Invoices[Type] = "Invoice" || Sales_Invoices[Type] = "Open Order"))
var Result = 
IF(Lastorderdate >=Firstdayofmonth,"Yes", "No")
RETURN 
Result

Thank you so much in advance! 

Gratefully, 

Christina

[technolog]

I hope this message finds you well. I've noticed that this solution remain unresolved. If any of you have managed to find a resolution to the issue, I kindly request that you share your solution for the benefit of the entire community. Alternatively, if you're still facing challenges, please do let us know as well.

Your insights and updates will greatly assist others who might be encountering the same challenge.

[Syk]

Are you sure the variable you're using "Lastorderdate" is the same as what you have in your column as last order date?
To test just temporarily change your return to lastorderdate instead of result

[Anonymous]

@Syk  Thank you for the response!

Oh wow, even though I used the same formula for the "Last Order Date" column, the variable in this formula is returning with 9/7/23 for all customers (which is the last posting date for all orders). 

I'm guessing I need to somehow summarize the formula to include the Customer Code.  Do you by chance, have any suggestions?

Thank you again! 

Christina

[Anonymous]

Hi @Anonymous 

You can try the following measure

Customer Ordered =
VAR Firstdayofmonth =
    EOMONTH ( TODAY (), -1 ) + 1
VAR Lastorderdate =
    CALCULATE (
        MAX ( Sales_Invoices[Posting Date] ),
        FILTER (
            ALLSELECTED ( Sales_Invoices ),
            [Customer Code]
                IN VALUES ( Sales_Invoices[Customer Code] )
                    && OR ( Sales_Invoices[Type] = "Invoice", Sales_Invoices[Type] = "Open Order" )
        )
    )
VAR Result =
    IF ( Lastorderdate >= Firstdayofmonth, "Yes", "No" )
RETURN
    Result

Best Regards!

Yolo Zhu

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

[Anonymous]

Hi  @Anonymous ,

Thank you for the response! 🙂 

I imputed the recommended formula:

Customer Ordered = 
var Firstdayofmonth = EOMONTH(TODAY(), -1) +1
var Lastorderdate = 
    CALCULATE(
        MAX(Sales_Invoices[Posting Date]), 
        FILTER(
            ALLSELECTED(Sales_Invoices),
            [Customer Code]
                IN VALUES (Sales_Invoices[Customer Code])
                    && OR (Sales_Invoices[Type] =  "Invoice", Sales_Invoices[Type] = "Open Order")
        )
    )
var Result = 
IF(Lastorderdate >=Firstdayofmonth,"Yes", "No")
RETURN 
Result

And got this as a result: 

Since I want to show all customers whether they have ordered or not - my Customer comes from my Dim CUSTOMER table, so I tried this: 

Customer Ordered = 
var Firstdayofmonth = EOMONTH(TODAY(), -1) +1
var Lastorderdate = 
    CALCULATE(
        MAX(Sales_Invoices[Posting Date]), 
        FILTER(
            ALLSELECTED(CUSTOMER),
            [Customer Code]
                IN VALUES (Sales_Invoices[Customer Code])
                    && OR (Sales_Invoices[Type] =  "Invoice", Sales_Invoices[Type] = "Open Order")
        )
    )
var Result = 
IF(Lastorderdate >=Firstdayofmonth,"Yes", "No")
RETURN 
Result

The formula does show "No" correctly, however my credit memos are now separate from my Invoices and Open Orders.

When I sort by customer code - you can see more clearly that credit memos are treated as separate from Invoices and Open Orders. (note:  credit memos are located in the same table 'Sales_Invoices' - under Type:  Credit Memo)

Seems like I need to do just a small tweak to the formula, but can't figure out what that is.  Any suggestions?

Thank you so much, again! 

Christina