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
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)

)

  • ElodiePignato's avatar
    ElodiePignato
    Regular Visitor
    2 years ago

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

      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