How to set expression for monthly averages ? ...

expressions
(Shivaprakash TS) #1

How to set expression for monthly averages ? How to select all the months in a year?

(Aleksi Alkio) #2

Without seeing the structure, you should first calculate the monthly value. When you have it, AVERAGE can calculate the value for you.

(Shivaprakash TS) #3

@Aleksi_Alkio Deaar Aleksi, I have maintained a file of my personal expenses and income on daily basis since past 4 years. I have the big list of data. If i calculate the average of months individually then it will be very big formula.

(Bellave Jayaram) #4

Create 2 VCs to hold the YEAR and MONTH,

then calculate monthly average value for each month, using a formula such as: AVERAGE(SELECT(Table[INCOME],AND(MONTH([DATE])=[_THISROW].[MONTH],YEAR([DATE])=[_THISROW].[YEAR])))

(Shivaprakash TS) #5

@Bellave_Jayaram Thanks Jayaram.

(Shivaprakash TS) #6

@Bellave_Jayaram Dear Jayaram, I tried the expression you given, but there is an error, text and number cannot compare with list. How to resolve this?

(Bellave Jayaram) #7

Can you copy and paste the exact formula you have? Also, what is the data type of the VCs?

(Shivaprakash TS) #8

@Bellave_Jayaram VC type is list for both month and year.

Average(Select(Master[Amount],And([Type]=“Expense”,[Month]=[_thisrow].[VM])))

I have a master sheet in which I listed all the data.

[Type] is a column in which I enter expense/income. [Month] is in master sheet in which i already extracted the month name from the date. [Year] is in master sheet in which I extracted year .

Now I am working in a Sheet called “average” to calculate expense average of months and year, there is no need to show the month in the table but i need tp calculate average of all the months in the sheet and average of years.

vc app formula for month Master[Month]