cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
Helper II

Need help with measure count

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.

 

Captu.PNG

My best regards. Thank you. 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Support
Community Support

Re: Need help with measure count

hi, @Hicham 

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

 

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

View solution in original post

6 REPLIES 6
Highlighted
Helper V
Helper V

Re: Need help with measure count

Hello @Hicham 

 

Can you try this?

 

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

Highlighted
Community Support
Community Support

Re: Need help with measure count

hi, @Hicham 

You could try this measure

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

 

Regards,

lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper II
Helper II

Re: Need help with measure count

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.

Highlighted
Community Support
Community Support

Re: Need help with measure count

hi, @Hicham 

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
Helper II
Helper II

Re: Need help with measure count

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. 

Highlighted
Community Support
Community Support

Re: Need help with measure count

hi, @Hicham 

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

 

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

View solution in original post

Helpful resources

Announcements
Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors