Average by month for a dimension measure

[Anonymous]

Best way to explain this is with an example.

For sales, the grain is the line item transaction.  These are associated with an order dimension and these orders have a fulfillment metric for the days between order placed and order fulfilled.

The transaction table has the order date tied to the master calendar.  When I created a measure AVERAGE(ORDERS[Days to Fulfillment])) I can get the overall average but when I add in the order date month, it shows this same overall average for each month.  I need the average fulfillment days for each month based on order date.

Hope I'm explaining this correctly.

[Icey]

Hi @Anonymous ,

Is this what you want?

If so, create your measure like so:

Average of fulfillment days 2 =
AVERAGEX (
    'FACT',
    DATEDIFF ( 'FACT'[Order date], 'FACT'[Complete date], DAY ) + 1
)

PBIX file attached.

Best Regards,
Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

No - jan has two orders of 15 & 7 days so the average is 15+7/2 = 11.  Where are you getting 11.57 ?

[Icey]

Hi @Anonymous ,

Try this:

DateDiff of day +1 = DATEDIFF ( MAX ( 'FACT'[Order date] ), MAX ( 'FACT'[Complete date] ), DAY ) + 1

Average = AVERAGEX ( VALUES ( 'FACT'[Order] ), [DateDiff of day +1] )

 

Best Regards,
Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[DS12345]

Hi @Anonymous ,

I think the solution to your problem is explained in this article:

https://community.powerbi.com/t5/Desktop/Calculate-an-average-from-a-Sum-by-Month/td-p/27250