Column that counts amount of rows between two dates

[Simkia]

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.

[v-zhangti · Accepted solution]

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 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.

[Simkia]

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

[Simkia]

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: 

https://docs.google.com/spreadsheets/d/160pNLsT3ZoI8jY8ilwzQ22qU4nrl1aCe/edit?usp=sharing&ouid=11372...

[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. 

[Ashish_Mathur]

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.