topic Re: Count Rows After a Date Specified on a Measure in DAX Commands and Tips

Count Rows After a Date Specified on a Measure

theotejada — Mon, 14 Dec 2020 15:50:12 GMT

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.

Re: Count Rows After a Date Specified on a Measure

AlB — Mon, 14 Dec 2020 15:59:55 GMT

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

Re: Count Rows After a Date Specified on a Measure

theotejada — Mon, 14 Dec 2020 17:52:24 GMT

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.

Re: Count Rows After a Date Specified on a Measure

AlB — Mon, 14 Dec 2020 18:09:08 GMT

theotejada

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

Re: Count Rows After a Date Specified on a Measure

theotejada — Tue, 15 Dec 2020 17:43:14 GMT

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.