Forum Discussion

ermuelle's avatar
ermuelle
Regular Visitor
8 years ago
Solved

Calculating average over multiple tables

I am trying to calculate a total monthly average from data in two seperate tables. This seems like it should be easy, but I cannot figure out the proper DAX formula. Please help! Here's what the data...
  • Ashish_Mathur's avatar
    Ashish_Mathur
    8 years ago

    Hi,

     

    You must create a calendar table and create a relationship from the Date column of the appended table to the date column of the Calendar Table.  In the Calendar Table use the =FORMAT(Calendar[Date],"mmmm") formula to extract the month name from the Date and =YEAR(Calendar[Date]) to extract the Year.  In your visual, drag the Year and Month from the Calendar Table.

     

    Hope this helps.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
8 years ago

Hi,

 

You must create a calendar table and create a relationship from the Date column of the appended table to the date column of the Calendar Table.  In the Calendar Table use the =FORMAT(Calendar[Date],"mmmm") formula to extract the month name from the Date and =YEAR(Calendar[Date]) to extract the Year.  In your visual, drag the Year and Month from the Calendar Table.

 

Hope this helps.

ermuelle's avatar
ermuelle
Regular Visitor
8 years ago

Thank you! I think I found a solution.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    8 years ago

    You are welcome.

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Community Support
    8 years ago

    ermuelle,

     

    By the way, you may help accept solution. Your contribution is highly appreciated.

  • yasminsalah's avatar
    yasminsalah
    New Member
    3 years ago

    Hi!

    I know it's been ages since this post. But would you happen to have the pbix file? I have almost the exact same question