Forum Discussion

DaleWatkins's avatar
DaleWatkins
Regular Visitor
5 years ago
Solved

Average by row count per month

Hi, 

 

Apologies if this makes no sense, 3rd time writing this... 

 

DAX newbie here trying to work out how to calculate an average of row count for 1 month over an x amount of years, i.e January 2018, 2019 and 2020?

 

I have a main table which is using count of number of tasks (id) and a date table. 

 

Thanks in advance for any assistance.

Need Help
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    5 years ago

    Hi DaleWatkins ,

     

    Sorry for that we are not clear about your issue.

    Do you want to calculate the average number of rows in the same month in three years?

    If yes, you can refer the following measure.

     

    Measure = 
var _count = COUNTROWS('Table')
var _count_year = DISTINCTCOUNT('Table'[Year])
return
DIVIDE(_count,_count_year)

     

     

    If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data?

    It will be helpful if you can show us the exact expected result based on the tables.

     

    Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

     

    Best regards,

     

    Community Support Team _ zhenbw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    DaleWatkins , with a date table and time intelligence

     

     

    MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))

    last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))

    2nd last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-24,MONTH)))

     

    Last three year Avg of month ([MTD Sales ]+[last year MTD Sales]+[2nd last year MTD Sales])/3

     

    You can filter on month or plot visual by month .

    • DaleWatkins's avatar
      DaleWatkins
      Regular Visitor
      5 years ago

      amitchandak

       

      Thanks for this but it's not quite working as I'd hoped... 

       

      Example:

      I have a total of 5328 rows and this is giving me an avg of 3076.

       

      When breaking it down monthly I'm getting a value of 3363 for September, which has a total row count of 757 for 2018 (248), 19 (198) and 20

      (311).

       

      The other issue I'll have with this formula is my data starts in July 2018 and runs to today and for the future. 

      • v-zhenbw-msft's avatar
        v-zhenbw-msft
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi DaleWatkins ,

         

        Sorry for that we are not clear about your issue.

        Do you want to calculate the average number of rows in the same month in three years?

        If yes, you can refer the following measure.

         

        Measure = 
var _count = COUNTROWS('Table')
var _count_year = DISTINCTCOUNT('Table'[Year])
return
DIVIDE(_count,_count_year)

         

         

        If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data?

        It will be helpful if you can show us the exact expected result based on the tables.

         

        Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

         

        Best regards,

         

        Community Support Team _ zhenbw

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.