if else conditions in dax format

[refint_650]

Hi All

how to rewrite in dax format.

if (A.Orderdate < @CalendarDate AND (A.purchasedate > @CalendarDate OR A.purchaseDate IS NULL))
OR (A.Orderdate > @CalendarDate) then count(id) else 0

vs

[TheoC]

Hi @refint_650 

Can you try something like the following calculated column:

Calc Column = 

VAR _OrderDate = YourTableName[Orderdate]
VAR _PurchaseDate = YourTableName[PurchaseDate]
VAR _CalendarDate = YourTableName[CalendarDate]

VAR _1 = _OrderDate < _CalendarDate
VAR _2 = _PurchaseDate > _CalendarDate || _PurchaseDate = 0
VAR _3 = _OrderDate > _CalendarDate

RETURN

IF ( _1 && _2 && _3 = TRUE , COUNT ( YourTableName[ID] ) , 0 )

You may need to make slight adjustments to get the syntax right but hopefully it works out.

Thanks heaps,

Theo

[refint_650 · Accepted solution]

@TheoC 

how can add i more filter condition  calendardate  = startofmonth(_OrderDate)

Calc Column = 

VAR _OrderDate = YourTableName[Orderdate]
VAR _PurchaseDate = YourTableName[PurchaseDate]
VAR _CalendarDate = YourTableName[CalendarDate]

VAR _1 = _OrderDate < _CalendarDate
VAR _2 = _PurchaseDate > _CalendarDate || _PurchaseDate = 0
VAR _3 = _OrderDate > _CalendarDate

RETURN

IF ( _1 && _2 && _3 = TRUE , COUNT ( YourTableName[ID] ) , 0 )

[TheoC]

@refint_650 will send it to you when I get in front of computer. on phone atm sorry.

[refint_650]

@TheoC 

appreciate for response 🙂

[TheoC]

@refint_650 did the formula work for you? If so, please mark as solution. 

[refint_650]

@TheoC 

It worked greate. but i forgot add one more condition to same logic

only show data where  calendardate  = startofmonth(_OrderDate)

[TheoC]

@refint_650 still on phone so apologies for any syntax errors! Hope this helps!

Calc Column = 

VAR _OrderDate = YourTableName[Orderdate]

VAR _PurchaseDate = YourTableName[PurchaseDate]

VAR _CalendarDate = YourTableName[CalendarDate]

VAR _StartOfMonth = STARTOFMONTH ( YourTableName[CalendarDate] )

VAR _1 = _OrderDate < _CalendarDate

VAR _2 = _PurchaseDate > _CalendarDate || _PurchaseDate = 0

VAR _3 = _OrderDate > _CalendarDate

VAR _4 = _StartOfMonth = _CalendarDate

RETURN

IF ( _1 && _2 && _3 && _4 = TRUE , COUNT ( YourTableName[ID] ) , 0 )

[refint_650]

@TheoC 

Much Appreciated. I will slightly tweak expression as year as primary & Month(optional)  as secondary  slicers .

ex: I'm doing VAR _1 = _OrderDate < selectedvalue( _CalendarDate)

[refint_650]

@TheoC 

I will give try and let u know  @TheoC  thank you.

vs

[TheoC]

All good! Look forward to hearing from you.