Forum Discussion

schoden's avatar
schoden
Post Partisan
6 years ago

COUNTIF

Hi All, 

 

I am confused why there is variance between these two approach.  To count the member where conditions are:

a. status = closed or resolved 

b. date is Yesterday

 

I created measure closed:

CLosed =
CALCULATE(DISTINCTCOUNT(table[member]), table[status]="Closed" || table[status]= "Resolved" && table[Date_Entered_UTC]= TODAY()-1 )
)
Result is
Second Approach which I dont prefer shows correct result: I have put page/visual filter.
 
 
I think my DAX wrong , it is not picking up the expression for date for Yesterday.
 
Thanks in advance.
 
Need Help

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    hi schoden You need something like this, in order to have the correct calculate.

    Whit variables is easier and clear. 

     

    mesuare =
    VAR lastday = PREVIOUSDAY('Table'[Date])
    var calculat = CALCULATE(SUM('Table'[value]), 'Table'[Date] = lastday)
    return calculat

     

    I leave you the .pbix here https://1drv.ms/u/s!AtEkAF7ffIsqg8RU_Q8H3f_rdntrBA?e=b8fdVl

    • schoden's avatar
      schoden
      Post Partisan
      6 years ago

      Anonymous Hi

       

      Thanks for sharing the file.   Previousday function takes back one day earlier any Day a user chooses in the date slicer. 

      But I want to set the date to YESTERDAY , user dont have to choose the date in slicer.  It is set to YESTERDAY automatically so when day goes , automatically it shows a day before today. 

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    schoden , Try like

    CLosed =
    CALCULATE(DISTINCTCOUNT(table[member]),filter(table, (table[status]="Closed" || table[status]= "Resolved") && table[Date_Entered_UTC]= TODAY()-1 )
    )

    • schoden's avatar
      schoden
      Post Partisan
      6 years ago

      amitchandak  Thanks for your response.

       

      yes I did it that way also  . It gives same result - with and without FILTER

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi schoden u need and if, in order to give a specific behavior with and without filter, try with this 

         

        mesuare = IF(SELECTEDVALUE('Table'[Date])>1,,SUM('Table'[value]), CALCULATE(SUM('Table'[value]), 'Table'[Date] = TODAY()-1))
         
        I leave you the .pbix
         
        thanks and regards