Forum Discussion
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 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
13 Replies
Hi,
Share some data to work with (in a format that can be pasted in an MS Excel file) and show the expected result.
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 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.
Hi,
I tried below daxI 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 advanceHi, Anonymous
try belowCumulative 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)
