COUNTIF

[schoden]

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.

[amitchandak]

@schoden , Try like

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

[schoden]

@amitchandak  Thanks for your response.

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

[Anonymous]

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

https://1drv.ms/u/s!AtEkAF7ffIsqg8RU_Q8H3f_rdntrBA?e=wdAivO

thanks and regards

[Anonymous]

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]

@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.