topic Re: DAX Formula in DAX Commands and Tips

DAX Formula

JB17 — Tue, 31 May 2022 06:47:04 GMT

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!

Re: DAX Formula

tamerj1 — Tue, 31 May 2022 07:13:11 GMT

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

Re: DAX Formula

ValtteriN — Tue, 31 May 2022 07:21:51 GMT

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/

Re: DAX Formula

v-xiaotang — Fri, 03 Jun 2022 08:33:57 GMT

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.