Dynamic monthly average DAX measure

[julienvdc]

Hey I have this visual as seen below, which shows me the amount of a new user accounts per months. I can filter by year(s) using a slicer that is applied on the user account creation date.

Info:

• The amount of accounts created is simply a user_id COUNT measure.
• the creation_date is in the same table as the user_id

I would like to display on the side as a card, the monthly average of new accounts.

EXAMPLE 1: Filtetring on 2023 & 2024 --> 15 months displayed & total of new users 2706 --> Average should be: 180

EXAMPLE 2: Filtetring on 2024 --> 3 months displayed & total of new users 334 --> Average should be: 111

This seems quite simple, but for some reasons I am not able to get the right formula... I managed to get it correct for 2024 but then something weird happens when I check other years and the average is way different than what it's supposed to be...

Any thoughts?

[PijushRoy · Accepted solution]

Hi @julienvdc 

Can you please try below DAX in measure

=
DIVIDE (
    COUNT ( YourDataTable[User_ID] ),
    DISTINCTCOUNT ( YourCalendarTable[YearMonth] )
)

Let me know if that works for you

If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the Thumbs Up 👍 button if this comment helps you.

Thanks
Pijush
Linkedin

[julienvdc]

Hey thank you so much for your quick reply! The creation date field is a date-time type of date so for the distinctcount to work I assume I should create a month-year column, no?

[PijushRoy]

Hi @julienvdc 

In your image, in x axis is shwoing month year
I hope you have month year column and you are using in visual
If not, you can create one or
Please create a pbix that contains sample data but still reflects your semantic model (tables, relationships, calculated columns, and measures). Upload the pbix to OneDrive, Google Drive, or dropbox and share the link in this thread. If you are using a spreadsheet to create/import the sample data instead of the manual import method, share the spreadsheet as well.

Let me know if that works for you

If your requirement is solved, please mark THIS ANSWER as SOLUTION ✔️ and help other users find the solution quickly. Please hit the Thumbs Up 👍 button if this comment helps you.

Thanks
Pijush
Linkedin