Forum Discussion

papanovn's avatar
papanovn
Frequent Visitor
9 years ago

Min date by expression evaluation disregarding date filter

Hello.

I have a table with sales. I have to find the first order date for every customer, but only for orders where amount is bigger than zero. I've used CALCULATE with ALL condition to find the first date disregarding any filters by period. When I include FILTER([Order]; SUM([Order Amount])>0) it stops working because the expression is affected by the date filter. Puting ALL in the expression filter doesnt help. Any ideas?

Here is my current measure. CALCULATE(MIN('Order'[Order Date]); ALL('Calendar'[Year]))
Here is something like what should i do:
CALCULATE(MIN('Order'[Order Date])

; ALL('Calendar'[Year])

; FILTER('Order Row'; SUM('Order Row'[Sold Amount User (EUR)])>0)

)

Need Help

3 Replies

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Microsoft Employee
    9 years ago

    Hi papanovn,

     

    Could you try the formula below? It should work.

    =
    CALCULATE (
        MIN ( 'Order'[Order Date] );
        FILTER ( ALL ( 'Order Row' ); SUM ( 'Order Row'[Sold Amount User (EUR)] ) > 0 )
    )

    If that is not the case, please post your table structures and relationships between your tables for better assistance.:smileyhappy:

     

    Regards

    • papanovn's avatar
      papanovn
      Frequent Visitor
      9 years ago

      Hi v-ljerr-msft,
      the matrix crashes with this measure. It calculates for long time and then says "something went wrong".
      I have added "Order Date" in "Order Row" so I have all the fields in one table now.

      • v-ljerr-msft's avatar
        v-ljerr-msft
        Microsoft Employee
        9 years ago

        Hi papanovn,

         

        For further troubleshooting this issue, could you share a sample pbix file which can reproduce the issue? You can upload it to Onedrive or Dropbox and post the link here. Do mask sensitive data before uploading.:smileyhappy:

         

        Regards