Converting Month into numbers and use it for dividing a formula (per month data)

[ManuSankaran]

hi, i need to know how to convert month (which is picked from a data source in SAP) into numbers and use it for dividing a total value. -- finding average per month

For example, the total activities for January is 10, February is 20. If i want to add these 2 values and get an average of January and February -- how do write the formula?

[Ashish_Mathur]

Hi,

Assuming you also have a year column, you should first create a Date column (assume the day to be 1).  Thereafter create a Calendar Table with calculated column formulas for Year, Month name and Month number.  Sort the Month name column by Month number.  Create a relationship (many to One and Single) from the Date column of the Data Table to the Date column of the Calendar Table.  From the Calendar Table, drag Year and Month name.  Select 1 year and 1 month.  Write these measures:

Total = sum(Data[Sales])

YTD average = averagex(filter(values(Calendar[month name]),datesbetween(calendar[date],date(year(min(calendar[date])),1,1),max(calendar[date]))),[Total])

Hope this helps.

[Arul]

@ManuSankaran ,

First you need to have separate dimesion date table to count the dates and then try the below formula,

Average = 
VAR _countOfActivitives = COUNT('Table'[Activities])
VAR _noOfDays = COUNTROWS(ALLSELECTED('Calendar'))
VAR _result = _countOfActivitives/_noOfDays
RETURN _result

Thanks,

Arul