Forum Discussion

julienvdc's avatar
julienvdc
Helper III
2 years ago
Solved

Dynamic monthly average DAX measure

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?

 

Need Help
Show and Tell
Tips and Tricks
  • PijushRoy's avatar
    PijushRoy
    2 years ago

    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

3 Replies

  • PijushRoy's avatar
    PijushRoy
    Community Champion
    2 years ago

    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's avatar
      julienvdc
      Helper III
      2 years ago

      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's avatar
        PijushRoy
        Community Champion
        2 years ago

        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