Need help with measure count

[Anonymous]

Hello everyone,

I'm doing a measurement that counts the number of tasks performed today. For the example, I put a copy for, so I circled all the start and end dates and I want to display that it actually, the current date, so for this example, the result is 3. 

Basically, a measure that allows you to count the number of tasks performed.

My best regards. Thank you. 

[v-lili6-msft]

hi, @Anonymous 

You could try this measure

measure = CALCULATE(COUNTROWS(Sheet1),FILTER(Sheet1,Sheet1[start time]<=TODAY() &&Sheet1[end time]>=TODAY() ))

Regards,

lin

[Anonymous]

Hi, @v-lili6-msft  

Thank you for your answer, but in this measure we are filtering just on date and counting rows on the table that you named Sheet1, but the purpose of this measure is to calculate the number of tasks that i notice is not shown in the measure?  Regards.

[v-lili6-msft]

hi, @Anonymous 

For some scenarios, COUNTROWS and COUNTA have the same logic.

So you could also try this formula:

measure = CALCULATE(COUNTA(Sheet1[tasks]),FILTER(Sheet1,Sheet1[start time]<=TODAY() &&Sheet1[end time]>=TODAY() ))

And you mean that ( just on date) the conditional is "=" not "<=" or ">=" in the formula?

If so you could just adjust it by yourself.

Best Regards,

Lin

[Anonymous]

Thank you @v-lili6-msft, I have another question if you have a scenario that uses a date time, for example, 5/3/2019 05:43:47 and we would like to apply this measure do you think it works? Imagine that a task was executed yesterday at 23:45:08 and we would like to show the number of tasks that were executed for the last 24 hours I guess the measure will change ?! My best regards. Thank you again. 

[v-lili6-msft · Accepted solution]

hi, @Anonymous 

For this scenario, you need to convert it to a date format not datetime format in the formula by DATE Function.

https://docs.microsoft.com/en-us/dax/date-function-dax

for example:

measure = CALCULATE(COUNTA(Sheet1[tasks]),FILTER(Sheet1,DATE(YEAR(Sheet1[start time]),MONTH(Sheet1[start time]),DAY(Sheet1[start time]))<=TODAY() &&DATE(YEAR(Sheet1[end time]),MONTH(Sheet1[end time]),DAY(Sheet1[end time]))>=TODAY() ))

and if you want filter datetime is last 24 hours, you could add a conditinal DATEDIFF in formula

https://docs.microsoft.com/en-us/dax/datediff-function-dax

For example:

measure = CALCULATE(COUNTA(Sheet1[tasks]),FILTER(Sheet1,DATEDIFF(Sheet1[end time],TODAY(),HOUR)<=24))

Best Regards,

Lin

[AndrejZitnay]

Hello @Anonymous 

Can you try this?

measurement  = CALCULATE(COUNTROWS(Sheet1),FILTER(Sheet1,Sheet1[start time]=TODAY() ))