cancel
Showing results for
Did you mean:
Frequent Visitor

## Column that counts amount of rows between two dates

Hello!

I would like to create a column in my Calendar table that counts how many active orders I have on each individual date. For this I have another table with orders and columns representing the order received date and one representing the order completed date. My idea is that I want to create a column in the Calendar table that checks the date of the row, and counts how many orders I have where:

Order received date <= the date of the row

And the date completed > than the date of the row.

Any suggestions?

1 ACCEPTED SOLUTION
Community Support

Hi, @Simkia

I intercepted 40 completed data calculations in your excel.

1. Create a new date table with the receipt time and completion time.

Date =
CALENDAR ( MIN ( 'Table'[Order received] ), MAX ( 'Table'[Order completed] ) )

2. Create a new column and calculate the orders in progress every day.

Count =
COUNTROWS (
FILTER (
'Table',
&& [Date] < 'Table'[Order completed]
)
)

3. If you want to count by week, you can add a column to calculate the week of the current date.

Week = WEEKNUM([Date],1)

4. The view is as follows.

Best Regards,

Community Support Team _Charlotte

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

5 REPLIES 5
Community Support

Hi, @Simkia

I intercepted 40 completed data calculations in your excel.

1. Create a new date table with the receipt time and completion time.

Date =
CALENDAR ( MIN ( 'Table'[Order received] ), MAX ( 'Table'[Order completed] ) )

2. Create a new column and calculate the orders in progress every day.

Count =
COUNTROWS (
FILTER (
'Table',
&& [Date] < 'Table'[Order completed]
)
)

3. If you want to count by week, you can add a column to calculate the week of the current date.

Week = WEEKNUM([Date],1)

4. The view is as follows.

Best Regards,

Community Support Team _Charlotte

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

Frequent Visitor

Thanks alot, this is exactly what I was looking for!

Frequent Visitor

I just assumed that it needed to be in the calendar table. Would be better if it was possible to simply create it as a measure. I would like something that looks similar to this, as to see if we're lowering or increasing the stack of orders per week in the last 10 weeks:

I have shared my data file, in a simplified version as there is alot of information that I cannot publicly share. I've made some dummy ID's and kept the dates which should be everything needed. For the excel file I have 4 different tabs, 3 with In Progress orders (No completed date) and 1 with completed orders (Both received and completed date) In PowerBI I've merged these tables.

Heres the data file:

Super User

Hi,

Share the PBI file as well in which you have already imported data from the Excel file.  Also, ensure that you have a Calendar Table in the PBI file with a week number column in the Calendar Table.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Super User

Hi,

Why do you want to calculate this in the Calendar Table.  This should directly be done in the visual with a measure.  Please share your datasets (in a form that can be pasted in an MS Excel file) and also show the expected result.

Regards,
Ashish Mathur
http://www.ashishmathur.com

Announcements