Average by months excluding current month

[sgeorge930]

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.

[FreemanZ]

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:

[amitchandak · Accepted solution]

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