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...
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.

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.

    • DaleWatkins's avatar
      DaleWatkins
      Regular Visitor
      5 years ago

      Sorry it wasn't clear but that's exactly what I was after, thanks v-zhenbw-msft

       

      I do have a follow up but I'll open a new thread for that, thanks again.

       

      Edit: sorry it doesn't look like it works on months with only 2 years of data.

       

      Edit: I was being special, just start my date table from the 1st month of data #facepalm.