How To Calculate Monthly Average for number of days?

[Nazish]

Hi,

I have one coulmn of open days and closthing month and year in seperate columns and I want to calculate the the monthly averge open days in power bi. Can you help how I can do that?

[amitchandak · Accepted solution]

@Nazish , Based on what I got. You need a month Year column

AverageX(Values(Table[Month year]), calculate(Sum(Table[Open Days])) )

[v-zhangti · Accepted solution]

Hi, @Nazish 

You can try the following methods.

Column:

Average =
CALCULATE (
    AVERAGE ( 'Table'[OpenedDays] ),
    FILTER (
        'Table',
        [Closed Month] = EARLIER ( 'Table'[Closed Month] )
            && [Closed Year] = EARLIER ( 'Table'[Closed Year] )
    )
)

Measure:

Average M =
CALCULATE (
    AVERAGE ( 'Table'[OpenedDays] ),
    FILTER (
        ALL ( 'Table' ),
        [Closed Month] = SELECTEDVALUE ( 'Table'[Closed Month] )
            && [Closed Year] = SELECTEDVALUE ( 'Table'[Closed Year] )
    )
)

Is this the result you expect?

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.

[Nazish]

Hi Community Team,

Yes that works, I was looking for the same output.

Thankyou.

[amitchandak · Accepted solution]

@Nazish , Based on what I got. You need a month Year column

AverageX(Values(Table[Month year]), calculate(Sum(Table[Open Days])) )

[Nazish]

@amitchandak So you mean I need to get Month year column first and then use below query?

How I can get Month Year Column? they are right now in 2 different columns.

Thanks.