topic Re: Conditional If statements including Dates in measure in DAX Commands and Tips

Conditional If statements including Dates in measure

ruesaint_denis — Mon, 24 Jan 2022 20:07:55 GMT

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?

Re: Conditional If statements including Dates in measure

ValtteriN — Mon, 24 Jan 2022 20:25:31 GMT

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!

Re: Conditional If statements including Dates in measure

ruesaint_denis — Mon, 24 Jan 2022 20:44:10 GMT

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()))

Re: Conditional If statements including Dates in measure

VahidDM — Mon, 24 Jan 2022 22:13:19 GMT

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/

Re: Conditional If statements including Dates in measure

ruesaint_denis — Mon, 24 Jan 2022 22:50:32 GMT

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']?

Re: Conditional If statements including Dates in measure

VahidDM — Mon, 24 Jan 2022 22:59:38 GMT

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/

Re: Conditional If statements including Dates in measure

Anonymous — Thu, 27 Jan 2022 07:12:02 GMT

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.