topic Re: Count Rows Falling between two dates ( from disconnected table ) in DAX Commands and Tips

Count Rows Falling between two dates ( from disconnected table )

sagar512 — Tue, 21 Dec 2021 13:02:01 GMT

Hi,

I am having two disconnected tables ( Task and Ticket).

On Task Table I need to count tickets (from the Ticket table) that fall between the task created date and Created +90 date.

In Excel, I used the below formula to get the expected output.

how can calculate the same in dax?

Task Table
Task Number	Category	Created	Created +90
Task 1	A	1-Jan-21	1-Apr-21
Task 2	A	1-May-21	30-Jul-21
Task 3	B	1-Sep-21	30-Nov-21
Task 4	B	1-Jun-21	30-Aug-21
Task 5	B	1-Dec-21	1-Mar-22

Ticket Table
Ticket Number	Created
Ticket 1	7-Jan-21
Ticket 2	2-Feb-21
Ticket 3	2-Feb-21
Ticket 4	6-Jun-21
Ticket 5	10-Jun-21
Ticket 6	19-Aug-21
Ticket 7	9-Sep-21
Ticket 8	13-Jun-21
Ticket 9	3-Mar-21
Ticket 10	9-Sep-21

Re: Count Rows Falling between two dates ( from disconnected table )

ValtteriN — Tue, 21 Dec 2021 10:04:06 GMT

Hi,

Here is one way to do this:

Dax (calculated column):

Tickets = calculate(COUNT(Tickets[TN]),DATESBETWEEN('Calendar'[Date],'Task Table'[Created],'Task Table'[C90]))

Data model:

End result:

I hope this helps and if it does consider accepting this as a solution and giving a thumbs up!

Re: Count Rows Falling between two dates ( from disconnected table )

sagar512 — Tue, 21 Dec 2021 10:11:32 GMT

Thanks for the response; but unfortunately I cannot use the calculate function in the column, as I am working with powerbi data set (direct query).

Any way to do this in measure?

Re: Count Rows Falling between two dates ( from disconnected table )

ValtteriN — Tue, 21 Dec 2021 12:19:25 GMT

Hi,

It works almost exactly the same with a measure:

tickets_ =

var _sdate = max('Task Table'[Created])

var _edate = max('Task Table'[C90]) return

calculate(COUNT(Tickets[TN]),DATESBETWEEN('Calendar'[Date],_sdate,_edate))

Re: Count Rows Falling between two dates ( from disconnected table )

smpa01 — Tue, 21 Dec 2021 12:26:57 GMT

sagar512 can you provide some sample data

Re: Count Rows Falling between two dates ( from disconnected table )

sagar512 — Tue, 21 Dec 2021 12:30:32 GMT

thanks, but the problem with the above solution is I need to always keep the lowest level (Task number) into the context.

my end goal is to build some chart that aggregates to month level etc, but calculation should always stay at task number level and then aggregate.

I think the column was the best fit in this scenario but unfortunately, I can't use it due to direct query 😞

Re: Count Rows Falling between two dates ( from disconnected table )

sagar512 — Tue, 21 Dec 2021 12:36:34 GMT

For some reason I don't see a way to attach powerbi file; so added my tables to the original post.

will this help?

Re: Count Rows Falling between two dates ( from disconnected table )

smpa01 — Tue, 21 Dec 2021 12:46:59 GMT

sagar512 try this measure

Measure = CALCULATE ( COUNT ( 'Table 2'[Ticket Number] ), DATESBETWEEN ( 'Table 2'[Created], MAX ( 'Table 1'[Created] ), MAX ( 'Table 1'[Created +90] ) ) )

Re: Count Rows Falling between two dates ( from disconnected table )

sagar512 — Tue, 21 Dec 2021 13:05:27 GMT

I added a category column into my data and this measure needs the lowest level (task number) to work.

it's not working as expected if I remove the task number.

the calculation should always work on task level and then aggregate per report context

Any way we can achieve this?

Re: Count Rows Falling between two dates ( from disconnected table )

smpa01 — Tue, 21 Dec 2021 13:18:45 GMT

sagar512 what do you mean?

Re: Count Rows Falling between two dates ( from disconnected table )

sagar512 — Tue, 21 Dec 2021 13:48:11 GMT

I get below; If I remove the lowest level and add category

Re: Count Rows Falling between two dates ( from disconnected table )

smpa01 — Tue, 21 Dec 2021 13:51:26 GMT

sagar512 when you are asking a question can you pleae make sure to include all the components? Do you wan to revise the previously provided sample data and include this new component?

Re: Count Rows Falling between two dates ( from disconnected table )

sagar512 — Tue, 21 Dec 2021 13:55:17 GMT

Added the new column to the data (original post).

Re: Count Rows Falling between two dates ( from disconnected table )

smpa01 — Tue, 21 Dec 2021 14:22:09 GMT

I still don't see an issue here

You need to display

'Table 1'[Created] and

'Table 1'[Created +90] in the viz for the DAX to evaluate in the filter context. Without having that axis present, the expression will not evaluate.

But if you do want to have that one displayed without bringing

'Table 1'[Created] and

'Table 1'[Created +90]

then you need an index column and do this with a new measure

Re: Count Rows Falling between two dates ( from disconnected table )

sagar512 — Tue, 21 Dec 2021 14:18:54 GMT

I think this is what the exact challenge is; to make this measure work without including task number or date (lowest level) into the viz.

I am using powerbi dataset, so I can't even make this work using the calculated column.

Re: Count Rows Falling between two dates ( from disconnected table )

smpa01 — Tue, 21 Dec 2021 14:24:37 GMT

sagar512 for some reason, my previous reply got edited and I have edited back my original reply. Please take a look above.

It is not an issue, it is doable. Check the new measure.