Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

12 months average

Hi all,   I have req as doing average for each month  Before I was showing count (I'd) for each date, mknth, year Now I want to show average like In Jan if I have count of id 700 then avg will b...
Need Help
Tips and Tricks
devesh_gupta's avatar
devesh_gupta
Impactful Individual
2 years ago

Anonymous You can try to create a measure like this:

12 Month Avg = CALCULATE(AverageX(Values('Date'[MONTH Year]),calculate(Sum('Table'[Value])))
	,DATESINPERIOD('Date'[Date ],MAX('Date'[Date ]),-12,MONTH))

 

If you find this insightful, please provide a Kudo and accept this as a solution.

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi,
I tried below dax 

I took

 

Cumulative Total =

CALCULATE(COUNT('MATTER TABLE'[ID]),

    FILTER (

        ALL ('Calendar Date'[Date]),

        'Calendar Date'[Date] <= MAX ( ('Calendar Date'[Date] ) )

    )

)

 

Average Test = DIVIDE([Cumulative Total],MAX('Calendar Date'[Month Sort]),0)

 

Firstly I took cumulative total cases, and after that i did average of it by dividing total cumulative with month number for respective months

I am doing it to get average of open matters per month in a particular year
The dax I used is showing correct count when I am selecting a particular year in open date
But i want it to show the correct average in trends manner for all years with correct average at a time not only for single year

Please Help

Thanks in advance

  • Dangar332's avatar
    Dangar332
    Resident Rockstar
    2 years ago

    Hi, Anonymous 

    try below

    Cumulative Total =

CALCULATE(COUNT('MATTER TABLE'[ID]),

    FILTER (

        ALL ('Calendar Date'[Date]),

        'Calendar Date'[Date] <= MAX ('Calendar Date'[Date] ) && 
        'Calendar Date'[year] = year(MAX ('Calendar Date'[Date] ))

    )

)

 

Average Test = DIVIDE([Cumulative Total],MAX('Calendar Date'[Month Sort]),0)
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi,

      it's giving below error 

      A single value for column 'YEAR' in table 'Calendar Date' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

      • Dangar332's avatar
        Dangar332
        Resident Rockstar
        2 years ago

        Hi, Anonymous 

        update code of all('calender date')

         

        Cumulative Total =

CALCULATE(COUNT('MATTER TABLE'[ID]),

    FILTER (

        ALL ('Calendar Date'),

        'Calendar Date'[Date] <= MAX ('Calendar Date'[Date] ) && 
        'Calendar Date'[year] = year(MAX ('Calendar Date'[Date] ))

    )

)

 

Average Test = DIVIDE([Cumulative Total],MAX('Calendar Date'[Month Sort]),0)