DAX question: Average of year over months

[denxx34]

Hi,

I can not do it 😑

What I have - a fact table and a dimCalendar.

What I want - calculate the average amount over year and display it over months.

Such as...

AvgPerMonth: = if (YEAR (Calendar.Date) = YEAR (today()); SUM(Amount) / DISTINCTCOUNT (YYYYMM); SUM (Amount) / 12))

Please help me. 🙄

This is the sample data:

Date		Amount
01.01.2018		23
02.01.2018		34
01.02.2018		56
02.02.2018		56
01.03.2018		34
02.03.2018		56
01.04.2018		23
01.05.2018		34
01.06.2018		56
01.07.2018		56
02.07.2018		34
02.08.2018		56
04.09.2018		23
05.09.2018		34
05.10.2018		56
07.10.2018		56
08.11.2018		34
03.12.2018		56
01.01.2019		34
02.01.2019		87
01.02.2019		56
02.02.2019		768
01.03.2019		67
02.03.2019		34
01.04.2019		98
01.05.2019		76
01.06.2019		55
01.07.2019		44
02.07.2019		98
02.08.2019		76
04.09.2019		55
05.09.2019		44
05.10.2019		98
07.10.2019		76
08.11.2019		55
03.12.2019		44
01.01.2020		56
05.02.2020		23
06.02.2020		76
03.03.2020		55
03.03.2020		44

I would like to have that:

[harshnathani · Accepted solution]

Hi @denxx34 ,

You can try this approach.

1. Create Calculated Columns

Year_ = YEAR('Table'[Date])

Month_ = MONTH('Table'[Date])

Create a measure

Average over Year =

var _sum = CALCULATE(SUM('Table'[Amount]), ALLEXCEPT('Table','Table'[Year_]))

var _countofmonths = CALCULATE(DISTINCTCOUNT('Table'[Month_]),ALLEXCEPT('Table','Table'[Year_]))

RETURN

DIVIDE(_sum,_countofmonths)

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)