Forum Discussion
Count Rows After a Date Specified on a Measure
I have a table with a similar look like this (we'll name this table as 'Sample'):
| Date/Time | Insignificant Values |
| 10/17/2020 05:02:38 PM | lorem |
| 10/17/2020 05:03:31 PM | ipsum |
| 10/17/2020 05:04:25 PM | dolor |
| 10/17/2020 05:06:12 PM | sit |
| 12/14/2020 04:30:57 AM | amet |
| 12/14/2020 04:34:43 AM | consectetur |
12/14/2020 04:39:42 AM | adipiscing |
| 12/14/2020 04:43:59 AM | elit |
| 12/14/2020 04:44:51 AM | sed |
What I want is a measure that will count the number of rows for this table if the date/time is beyond a date/time placed on a measure (in this case, [M_ReferenceDate] = 12/14/2020 03:30:53 AM).
I am expecting a count of 5 on this example, but I'm getting the count of the entire rows (9) without satisfying the condition using the formula COUNTROWS(FILTER('Sample','Sample'[Date/Time] > [M_ReferenceDate])). I even tried creating a column with expression IF('Sample'[Date/Time] > [M_ReferenceDate], 1, 0) to see if I can have this as backup (then counting the 1s), but it results to all rows being 1 instead of only the last 4.
I'm still a beginner on Power BI, and searched for various solutions related to this one, but those solutions still results to the same problem.
Works fine on my side on the data you provided earlier. See it all at work in the attached file.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
4 Replies
- AlB
Community Champion
Works fine on my side on the data you provided earlier. See it all at work in the attached file.
Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi AlB,
Actually, it's my data/Power BI acting up, not leading to the expected results. I tried to make another Power BI Dashboard using the same data, and it works as expected. I don't know why, but I think that will be the solution for now.
Thanks for your help.
- AlB
Hi theotejada
Are you sure [M_ReferenceDate] is of type datetime? Is the Sample[Date/Time] of type datetime?
Try this, and it should work with the rest as you have it:
M_ReferenceDate = CONVERT("12/14/2020 03:30:53 AM", DATETIME)Please mark the question solved when done and consider giving a thumbs up if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
Hi AlB
I am sure that [M_ReferenceDate] is of type DATETIME. I had a prior calculation having the value of [M_ReferenceDate], which is:
M_ReferenceDate = CALCULATE(MAX('Another Table'[Date/Time]), FILTER('Another Table', 'Another Table'[Status] = "Started"))Where Another Table has a sample value below: Note: I'm getting posting problems whenever I tried to insert a table, so I just made it manually below
Date/Time Status
08/15/2020 12:49:32 PM Started
08/15/2020 02:12:47 PM Ended
09/15/2020 06:59:06 PM Started
09/15/2020 08:36:09 PM Ended
11/11/2020 11:46:11 AM Started
11/11/2020 12:24:01 PM Ended
12/14/2020 03:30:53 AM Started
12/14/2020 05:05:25 AM Ended
The above calculation for [M_ReferenceDate] gets the highest value, in this case, 12/14/2020 03:30:53 AM and is formatted as DATETIME.
