Conditional If statements including Dates in measure

[ruesaint_denis]

Hello

I would like to create a measure that does the following: 

IF:

Table['Order Created At'] >= Date(2022, 1, 19) && Table['Company Name] = "Stripe"

then: 1500*[Num of Orders]

ELSE IF:

Table['Order Created At'] >= Date(2022, 1, 14):

then: 1000*[Num of Orders]

else: 

Blank

Essentially, if someone ordered "Stripe" on or after 1/19/2022, then calculate number of orders * 1500,

if someone ordered anything, regardless of company name, on or after 1/14/2022, then calculate number of orders * 1000. 

I placed the 1/19 if statement before the 1/14 if statement, because I want that one to execute first. 

Would anyone know how this is possible for a measure? 

[Anonymous]

Hi @ruesaint_denis ,

You can write the measure formula directly like this, the returned result is as you expect.

Measure =
IF (
    MAX ( 'Table'[Order Created At] ) >= DATE ( 2022, 1, 19 ),
    "Result1",
    IF (
        MAX ( 'Table'[Order Created At] ) >= DATE ( 2022, 1, 14 ),
        "Result2",
        "Result3"
    )
)

Best Regards,

Stephen Tao

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

[VahidDM]

HI @ruesaint_denis 

Try this masure:

Measure = 
Var _DOC = MAX ( Table['Order Created At'] )
Var _CN = MAX ( Table['Company Name] )
Var _NO = [Num of Orders] -- if the Num of Orthers is Measure not column

Var _Date1 = Date(2022, 1, 19)

Var _Date2 = Date(2022, 1, 14)
return
IF ( _DOC >= _Date1 && _CN = "Stripe", 1500*_NO ,IF ( Var _DOC >= _Date2, 1000*_NO , BLANK() )

If you face any issues, please share a sample of your data and the expected result in text format.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[ruesaint_denis]

@VahidDM @ValtteriN , 

maybe the problem has to do with my data model? 

These are the measures I am using: 

and how my tables would fit into one of your solutions: 

IF measure = 
Var _DOC = MAX ( 'Date Table'[Date] )
Var _CN = MAX ( Orders[Company Name])
Var _NO = [Num of Orders (when placed, not incl canceled)]
Var _Date1 = Date(2022, 1, 19)
Var _Date2 = Date(2022, 1, 14)
return
IF ( _DOC >= _Date1 && _CN = "Ripple", 1500*_NO ,IF (_DOC >= _Date2, 1000*_NO , BLANK() ))

Is it something to do with the inactive relationship between the date and order table that is messing with my ability to filter by Orders['Company Name']? 

[VahidDM]

Can you share your PBIX file or a sample of your data table and result that you are looking for in a text format? @ruesaint_denis '

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[ValtteriN]

Hi,

Here is an example on how to do this with test data:

Dax (the conditions are not excatly the same bu the logic is applicable):

IF measure =

var _cdate = MAX(Deals[Created at]) return

IF(_cdate<date(2020,1,1)&&MAX(Deals[Opp Name])="Oracle",100,IF(_cdate=DATE(2020,11,10),10,BLANK()))

End result:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

[ruesaint_denis]

Hi @ValtteriN , 

Thank you, for some reason it's not working, and calculating everything currently as the Num of Orders*1000. It's not registering the Num of Orders*1500 clause:

IF measure = 
var _cdate = MAX('Date Table'[Date]) 
return
IF(_cdate>DATE(2022,1,13),[Num of Orders (when placed, not incl canceled)]*1000,
IF(_cdate>date(2022,1,18)&&MAX('Orders'[Company Name])="Ripple",[Num of Orders (when placed, not incl canceled)]*1500,
BLANK()))