cancel
Showing results for
Did you mean:
Frequent Visitor

## average of created for the last 12 months

Hi

I am looking for some help with DAX calculation. I have a list of "Created Dates" and im looking to achieve something that calculates a count per month and then average of these over the last 12 months please?

Thanks

Example:

1 ACCEPTED SOLUTION
Super User

Hi, @MrMED87

Please check the below picture and the sample pbix file's link down below.

Count ID =
IF (
ISFILTERED ( AxisTable[Index] ),
SWITCH (
SELECTEDVALUE ( AxisTable[Month Name] ),
"Average",
DIVIDE (
CALCULATE ( COUNTROWS ( IDs ), ALL ( IDs ) ),
CALCULATE ( COUNTROWS ( VALUES ( Dates[Month Name] ) ), ALL ( Dates ) )
),
FORMAT ( COUNTROWS ( IDs ), "#,##0" )
)
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

2 REPLIES 2
Regular Visitor

Hi JiHwan, I have this same exact issue I am trying to solve. But I am doing this in Excel with Power Pivot. I have created the AxisTable and made the relationship. When I attempted to create the measure I found that 'SELECTEDVALUE ' is not recognized. I am using O365 so I'm surprised it doesn't resolve. Is there another option here for my scenario?

Thanks

Tony

Super User

Hi, @MrMED87

Please check the below picture and the sample pbix file's link down below.

Count ID =
IF (
ISFILTERED ( AxisTable[Index] ),
SWITCH (
SELECTEDVALUE ( AxisTable[Month Name] ),
"Average",
DIVIDE (
CALCULATE ( COUNTROWS ( IDs ), ALL ( IDs ) ),
CALCULATE ( COUNTROWS ( VALUES ( Dates[Month Name] ) ), ALL ( Dates ) )
),
FORMAT ( COUNTROWS ( IDs ), "#,##0" )
)
)

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Announcements