12 months average

[Anonymous]

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 be counted based on month num

700/1 = 700

For Feb....( Jan I'd count+ Feb I'd count)/2

It's like sum of prev month id counts and present month divided by month num

It continues till December

Date column have dates from many years , from 1990

I'm not getting exactly how to implement in pbi

Please help

Thanks in advance 

[devesh_gupta]

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

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]

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]

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]

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)

[Anonymous]

Thanks for replying

calculation error in measure [ Cumulative Total] :dax comparision operation do not support comparing values of type text with values of type integer. Consider using VALUE or FORMAT Function

I tried foemat and value also, shoiwng 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.
Please reply back

Thanks

[Dangar332]

Hi, @Anonymous 
put screen short of error with dax code

[Anonymous]

[Dangar332]

hi, @Anonymous 

update code of  all('calender date')

remove update code of all('calender date'[date])  -- remove red part from all and put  all('calender date')

[Anonymous]

Hi, Thanks for replying

when I select 2022 year the avearge of each month is like below

When I select 2023 in year (open_date) the 12 months rolling average is like below

But when I didn't give any filters and want to check respective years 12 months rolling averages it's changing , like below

I want to show rolling avearges for every year separately in trends 

Please reply
Thanks in advance

[johnyip]

@Anonymous You need to provide the sample data here for the assistance.

From your question, the general guideline is to create a virtual table in a measure for the purpose to scan for the month (as the denominator), and as well calculate the count of your records (nominator) in the same measure.

Finally, returning the answer as nominator/denominator.

[Anonymous]

I provided, please check 

[Ashish_Mathur]

Hi,

Share some data to work with (in a format that can be pasted in an MS Excel file) and show the expected result.