Forum Discussion

superhayan's avatar
superhayan
Icon for Helper I rankHelper I
2 years ago
Solved

COUNT DAX with Date Conditions

Hello all, I am stuck in a COUNT DAX with date conditions and hopefully the experts here can give me a helping hand.   I have a table of compliance cases which only has 4 columns: Case ID, Case Cre...
"Need Help"
  • Anonymous's avatar
    Anonymous
    2 years ago

    Thanks JamesFR06  for the quick reply!

     

    Hi superhayan ,

    (1) My test data is the same as yours.

    (2) We can create measures.

    Count_backlog = COUNTROWS(FILTER(ALLSELECTED('Compliance'),[Case Create Date]<=MAX('DateTable'[Date]) && [Current Status] <> "Closed"))
    Count_Closed = CALCULATE(COUNT(Compliance[Compliance Case ID]),FILTER(ALLSELECTED('Compliance'),'Compliance'[Case Close Date]=MAX('DateTable'[Date]) && 'Compliance'[Current Status]="Closed"))

    (3) Then the result is as follows.

     

    If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

     

    Best Regards,

    Neeko Tang

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

JamesFR06's avatar
JamesFR06
Icon for Resolver IV rankResolver IV
2 years ago

Hi,

 

Hope this is what you want :

CreatedOC = DISTINCTCOUNT('Table (2)'[Compliance Case ID])
Openprjct =
Var mindate=min('Date'[Date])
Var Maxdate=Max('Date'[Date])
var result=countrows(filter('Table (2)','Table (2)'[Case Create Date]>=Mindate&&'Table (2)'[Case Create Date]<=Maxdate&&'Table (2)'[Current Status]="Closed"&&'Table (2)'[Case Close Date]>Maxdate))
return
result
  • superhayan's avatar
    superhayan
    Icon for Helper I rankHelper I
    2 years ago

    Thanks for your reply but sadly it gives a blank line chart. I pulled the formula to a Card visual and it showed "--". I am suspecting its because the x-axis (calendar) is related to Case Close Date and only cases that are "Closed" has a close date, so when we only count NON-Closed cases so it returns nothing?