Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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 t...
Need Help
  • v-zhangti's avatar
    v-zhangti
    4 years ago

    Hi, Anonymous 

     

    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.

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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=113721970644725624567&rtpof=true&sd=true

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    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.