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
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors