Forum Discussion
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?
Thanks in advance.
Hi, Anonymous
I intercepted 40 completed data calculations in your excel.
- 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 received] && [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
- Ashish_Mathur
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.
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:
- Ashish_Mathur
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.
v-zhangtiCommunity Support
Hi, Anonymous
I intercepted 40 completed data calculations in your excel.
- 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 received] && [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.
