measure average age per order

[JC2022]

Hi,
in my powerbi report I have this measure called "# Open Orders EOP" where I measure how many open orders we have per week. Now I also want to add the average age per open order. The result should look like column E from the screenshot below.
Column A, B, C and D is what I have in my powerbi report, but I want to add column E (the individual values are maybe not correct).

measure1

OpenOrdersWoW =

VAR MinDate = MIN ( 'dim_date'[Date] )

VAR MaxDate = MAX ( 'dim_date'[Date] )

VAR Result =

    CALCULATE (

        COUNTROWS ( tableA ),

        tableA[CreatedDate] <= MaxDate,

        OR(

            tableA[ClosedDate] > MinDate,

            ISBLANK(tableA[ClosedDate])

        ),

        REMOVEFILTERS ( 'dim_date' )

    )

RETURN

    Result

measure2 (column D)

# Open Orders EOP =

CALCULATE (

    [OpenOrdersWoW],

    LASTDATE ( 'dim_date'[Date] )

)

[V-yubandi-msft · Accepted solution]

Hi @JC2022 ,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[JC2022]

I think I should create a measure, something like this:

DaysOpen =

VAR CreatedDate = MAX(tableA[CreatedDate])

VAR ClosedDate = MAX(tableA[ClosedDate])

VAR LastDateInPeriod = LASTDATE('dim_date'[Date])

RETURN

    IF(

        ISBLANK(ClosedDate),

        DATEDIFF(CreatedDate, LastDateInPeriod, DAY), 

        IF(

            ClosedDate <= LastDateInPeriod,

            DATEDIFF(CreatedDate, ClosedDate, DAY),

            DATEDIFF(CreatedDate, LastDateInPeriod, DAY)

        )

    )

But then the result is this (example of 1 order):


the total of 25 is correct because this one closed in the week of 202501 and the 2 for 202449 is also correct. But why do I have missing values for 202450 (should have value 9), 202451 (should have value 16) and 202552 (should have value 23)? It shows only for the "first week open" of the order.

[V-yubandi-msft]

Hi @JC2022 ,

When an order spans multiple Year Week values, simply averaging or changing summarization to  Average  won’t work, because DaysOpen is only calculated for the first week and remains blank afterward.

To get the correct average age per open order per week, you need a measure that

1. Calculates DaysOpen for each order across all weeks it was open.
2. Filters orders to only include those with # Open Orders EOP = 1 in that specific week.
3. Averages the DaysOpen for this filtered set.

This ensures the calculation reflects the true open order duration instead of missing values skewing the result.

I Hope this helps.  If my response resolved your query, kindly mark it as the Accepted Solution to assist others. Additionally, I would be grateful for a 'Kudos' if you found my response helpful.

[Deku]

Right click on the column on values well of the matrix and swap summarization from sum to average or create a measure

Average( table[age per open order])

[JC2022]

no, I have tried this already. It will have missing values, possibly because some orders are present in multiple Year_Week. The average should only be caluclated for the "# Open Orders EOP" = 1 per week.