cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Simkia
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? 

Thanks in advance.

1 ACCEPTED SOLUTION
v-zhangti
Community Support
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 received]
            && [Date] < 'Table'[Order completed]
    )
)

 

vzhangti_0-1637117663125.png

 

    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)

 

vzhangti_1-1637117713388.png

 

    4. The view is as follows.

vzhangti_2-1637117741934.jpeg

 

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.

View solution in original post

5 REPLIES 5
v-zhangti
Community Support
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 received]
            && [Date] < 'Table'[Order completed]
    )
)

 

vzhangti_0-1637117663125.png

 

    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)

 

vzhangti_1-1637117713388.png

 

    4. The view is as follows.

vzhangti_2-1637117741934.jpeg

 

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
Frequent Visitor

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

Simkia
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:

Simkia_0-1636976217877.png

 

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

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
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
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
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Release May 2022 768x460.png

Check it out!

Click here to read more about the May 2022 updates!