Average by row count per month

[DaleWatkins]

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.

[amitchandak]

@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]

@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 · Accepted solution]

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]

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.