DAX formula - Average

[ElodiePignato]

Hello everyone !

I have an issue with a DAX formula.

I have a table named "Incomind documents". It's one row per ticket (workload), a column with the status (done, on hold, to treat, etc.) and a column with the reception date.

I would like to calculate the average of incoming tickets for the last 12 months.

Any idea how to do that ?

Thanks !

[Dangar332]

hi, @ElodiePignato 

result =
 averagex(
     filter('Incomind documents',
        dateadd('Incomind documents'[reception date],-1,year)
            ),
     'Incomind documents[workload]
          )

[ElodiePignato]

Thank you @Dangar332 but I have this error message :

"The function AVERAGEX cannot work with values of type String.

[Dangar332]

hi, @ElodiePignato 

i think your   'incomind documents'[workload]  is string type. so it could happen

can you provide some sample data?

[ElodiePignato]

I finally used this measure:

But it's showing me the exact same result than another formula where I calculate the incoming tickets from last month using "COUNTROWS" :

[Dangar332]

HI, @ElodiePignato 

as i understand you want to avg of last 12 month with beginning of month of today
if yes then follow below

result =
var a = eomonth(today(),-13)+1   //  this give you  (11/1/2022)  as you want beginning of month of today with 12 month difference


var b = calculate(
          countrows(yourtablename),
          'Incomind documents'[reception date]>=a &&
          'Incomind documents'[reception date]<=today()
                  )  // give rows b/w  (11/1/2022 to 11/2/2022)

return 
divide(b,12)

)

[ElodiePignato]

Hi @Dangar332 this formula gives a whole different result :

The previous DAX formula was "better".

I checked with the table "Incoming documents" via Excel and this is the numer of incoming tickets per month :

So the average should be around 5k

[Dangar332]

hi, @ElodiePignato 

try to update previous formula

result =
var a = eomonth(today(),-13)+1  
 return
averagex(
     filter('Incomind documents',
        'Incomind documents'[reception date]>=a &&  
        'Incomind documents'[reception date]<=today()
            ),
     'Incomind documents[workload]
          )

if not work then provide some sample data