Forum Discussion

LABrowne's avatar
LABrowne
Icon for Helper II rankHelper II
2 years ago

DAX: Count based on Multiple criteria

Hi there,

 

Currently working on a formula where I need to count the number of orders based on following criteria: (1) the order saturation is over 100% and (2) the order is between it's delivery date (seperate column) and expiry date (again another seperate column). So far I have the following formula meeting criteria (1) but need to include (2) into the formula, please see below:

 

Active Order Count (Order Saturation > 100%) = COUNTX(VALUES(Order[OrderNumber]),IF([OrderSat] > 1,1))
 
Any help amending the formula to add an additional criteria to also only count orders between their delivery and expiry date would e much appreciated!
 
Kind regards,
Luke

2 Replies

  • Walter_W2022's avatar
    Walter_W2022
    Icon for Resolver II rankResolver II
    2 years ago

    Hi LABrowne please try below, you can tweak it to fit your needs

    Active Order Count (Order Saturation > 100%) = COUNTX(VALUES(Order[OrderNumber]),IF([OrderSat] > 1 && DATEDIFF ( [delivery date], [expiry date], DAY )>=0,1))

    • LABrowne's avatar
      LABrowne
      Icon for Helper II rankHelper II
      2 years ago

      Hi Walter,

       

      Thanks for your assistance but it is only letting me select measures where as delivery date and expiry date are not measures, I'm not sure why?

       

      Thanks,

      Luke