DAX Formula

[JB17]

hi, good day! I'm newbie to powerbi. Can someone help me with DAX formula? I was trying to get the average per month wherein there are four scores per month. Similar to below table but for 12 months.

Would really appreciate your help! Thanks in advance!

[v-xiaotang · Accepted solution]

Hi @JB17 

You can try this,

average = 
var _date= MIN('Table'[Date])
return AVERAGEX(FILTER(ALL('Table'),YEAR( 'Table'[Date])= YEAR(_date) && MONTH('Table'[Date])= MONTH(_date)),[Score])

result

Best Regards,

Community Support Team _Tang

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

[ValtteriN]

Hi,

You can create a relationship between your fact table and calendar table and then use DAX similar to this:

Monthly average =

var _year = YEAR(MAX('Calendar'[Date]))

var _month = MONTH(MAX('Calendar'[Date]))

return

CALCULATE(AVERAGE(Aggregation[Duration(Secs)]),ALL('Calendar'[Date]),

'Calendar'[Year]=_year,

'Calendar'[Month]=_month)


Here we use ALL to remove date filter and then use variables to return the context on month and year level. By doign this we can get monthly averages.




I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

[tamerj1]

Hi @JB17 
Please provide more details about your source data. It really depends