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.

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

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.