Showing average time calculation based on completed date

[GA1993]

Hello,

How do I show the average number of days of a certain month depending on the "Completed Date" field even though it is filtered as differently as shown below?

For example, I have selected December 2021, based on the report details only 1 order was completed with 20 days as the average time. However, as seen below, it accounts for the "Order Date" field, hence, calculating (32+20+26)/3 = 26 days.

I would like to show it as:

December 2021 - 20 ave days for 1 order

January 2022 - 29 ave days for 2 orders (32+26)/2 = 29

[GA1993]

How do I insert a pbix file here? I couldnt find an icon to click...

[amitchandak]

@GA1993 , Based on what I got

calculate( AverageX(Values('Date'[Month Year]) , [Total Days]) , allselected())

[GA1993]

This is what I got 😕

[amitchandak]

@GA1993 ,
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[GA1993]

Here you go... 

https://www.dropbox.com/s/3ncac3qsju6nc5d/Carry%20over%20of%20data%20values%20into%20next%20month%20... 

[v-chenwuz-msft · Accepted solution]

Hi @GA1993 ,

Create a inactive relationship between sheet1 and tac_cal on date and completed date.

Then create a measure named AVG.

AVG = 

AVG = CALCULATE(AVERAGE(Sheet1[new total days]),USERELATIONSHIP(Tab_cal[Date],Sheet1[Completed Date]))

Result:

Pbix file in the end you can refer.

Best Regards

Community Support Team _ chenwu zhu

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