topic Re: Average by months excluding current month in DAX Commands and Tips

Average by months excluding current month

sgeorge930 — Fri, 17 Feb 2023 21:21:58 GMT

My brain has not been working properly when thinking of DAX, any advice would be appreciated.

I would like to create a measure to calculate the average number of credits used per month excluding the current month.

For example:

Date	Credits used
12/15/2022	5
12/17/2022	7
1/6/2023	9
2/4/2023	4

So then it would need to summerize by month and then average per month excluding February as the current month.

The result would be: 10.5 credits used on average per month (excluding current month)

There must be an easy way to write this in DAX, I just have been annoyingly stuck on this fairlyy simple one.

Re: Average by months excluding current month

amitchandak — Sat, 18 Feb 2023 03:25:30 GMT

sgeorge930 , prefer to use a date table joined date of you table having month year

calculate(

Averagex(Values(Date[Month Year]), calculate(Sum(Table[Credit Used])) ) , filter(all(Date), eomonth(date[Date],0) <> eomonth(today(),0) ) )

to get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

Re: Average by months excluding current month

FreemanZ — Sat, 18 Feb 2023 04:49:39 GMT

hi sgeorge930

1) try to add a column like:

Month = FORMAT([Date], "YYYYMM")

2) write a measure like:

Measure = AVERAGEX( CALCULATETABLE( VALUES(TableName[Month]), TableName[Month] < FORMAT(TODAY(), "YYYYMM") ), CALCULATE(SUM(TableName[Credits])) )

it worked like: