Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now
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.
Solved! Go to 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
hi, @Anonymous
You could try this measure
measure = CALCULATE(COUNTROWS(Sheet1),FILTER(Sheet1,Sheet1[start time]<=TODAY() &&Sheet1[end time]>=TODAY() ))
Regards,
lin
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.
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
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.
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
Hello @Anonymous
Can you try this?
measurement = CALCULATE(COUNTROWS(Sheet1),FILTER(Sheet1,Sheet1[start time]=TODAY() ))
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
113 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |