DAX measurement for two dates in same table and using filter

[alialsayer]

I have one table and I have a condition to calculate 

To calculate order by US $ with:

1. delivered condition is Yes

2. any order date equal or greater than the selected date ( 7th of Jule)

3. any shipped date equal or greater than the selected date ( 7th of Jule)

To calculate Shipped by US $ with:

1. delivered condition is Yes

2. any shipped date equal or greater than the selected date ( 7th of Jule)

Note: I built a calendar table where the start date is equal to order date and the last date is the maximum shipped date

please if anyone could help 

[v-xiaotang · Accepted solution]

Hi @alialsayer 

if 

use measure like bellow:

measure1 = 
VAR _seldate=SELECTEDVALUE('calendar'[Date])
RETURN
CALCULATE(Sum('Table'[US]), FILTER('Table',('Table'[Order Date] >=_seldate || 'Table'[Shipped Date] >=_seldate ) && 'Table'[Deliver] = "Yes"))

if

then

measure1 = 
VAR _seldate=SELECTEDVALUE('calendar'[Date])
RETURN
CALCULATE(Sum('Table'[US]), FILTER('Table',('Table'[Order Date] >=_seldate && 'Table'[Shipped Date] >=_seldate ) && 'Table'[Deliver] = "Yes"))

-

measure2 = 
VAR _seldate=SELECTEDVALUE('calendar'[Date])
RETURN
CALCULATE(Sum('Table'[US]), FILTER('Table','Table'[Shipped Date] >=_seldate  && 'Table'[Deliver] = "Yes"))

-

result:

Best Regards,

Community Support Team _ Tang

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

[alialsayer]

Thanks, it works with me, I really appreciate your help and your attached example make this easy to understand 🌹

[amitchandak]

@alialsayer , with help from independent date table

measure 1=
VAR minDate = MINX( allselected('Calendar') , 'Calendar'[Date] )
VAR maxDate = MAXX(allselected('Calendar') , 'Calendar'[Date] )
RETURN
CALCULATE(Sum(Table[US$]), filter( Table,Table[Order Date] >=_min && Table[Shipped Date] >=_max && Tbale[delivered] = "Yes"))



measure 2 =
VAR minDate = MINX( allselected('Calendar') , 'Calendar'[Date] )
VAR maxDate = MAXX(allselected('Calendar') , 'Calendar'[Date] )
RETURN
CALCULATE(Sum(Table[US$]), filter( Table, Table[Shipped Date] >=_max && Table[delivered] = "Yes"))

[alialsayer]

Thank you, I try these VAR measures but it doesn't work for me. It gives me wrong numbers