Forum Discussion

alialsayer's avatar
alialsayer
Icon for Helper I rankHelper I
5 years ago
Solved

DAX measurement for two dates in same table and using filter

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 

Need Help
  • v-xiaotang's avatar
    v-xiaotang
    5 years ago

    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.

4 Replies

  • v-xiaotang's avatar
    v-xiaotang
    Icon for Community Support rankCommunity Support
    5 years ago

    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's avatar
      alialsayer
      Icon for Helper I rankHelper I
      5 years ago

      Thanks, it works with me, I really appreciate your help and your attached example make this easy to understand ðŸŒ¹

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    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's avatar
      alialsayer
      Icon for Helper I rankHelper I
      5 years ago

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