Forum Discussion

julienvdc's avatar
julienvdc
Icon for Helper III rankHelper 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: T...
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

PijushRoy's avatar
PijushRoy
Icon for Community Champion rankCommunity 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
    Icon for Helper III rankHelper 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
      Icon for Community Champion rankCommunity 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