Forum Discussion

ElodiePignato's avatar
ElodiePignato
Regular Visitor
2 years ago

DAX formula - Average

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.) an...
Need Help
Dangar332's avatar
Dangar332
Resident Rockstar
2 years ago

hi, ElodiePignato 

 

 

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

 

ElodiePignato's avatar
ElodiePignato
Regular Visitor
2 years ago

Thank you Dangar332 but I have this error message :

 

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

  • Dangar332's avatar
    Dangar332
    Resident Rockstar
    2 years ago

    hi, ElodiePignato 

     

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

    can you provide some sample data?

    • ElodiePignato's avatar
      ElodiePignato
      Regular Visitor
      2 years ago

      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's avatar
        Dangar332
        Resident Rockstar
        2 years ago

        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)

        )