14 Replies Latest reply on Oct 20, 2016 10:53 AM by Mahfooj Khan

# How to calculate Latest month average?

Hi all, Need help on below

I have a dimension as Period with values 1609, 1608, 1607 and so on, i have 2 measures with Actual and total. I use these measures to calculate avg as Actual/Total

My requirement is, i have a Landing page in dashboard and this page should be referring to latest months data (in this case 1609 (September 2016), i want this calculation should be dynamic since in November we will have data for 1610 (October 2016). I want this landing page should automatically update when ever new month is added to Period dimension.

• ###### 1. Re: How to calculate Latest month average?

Can you share your workbook?

• ###### 2. Re: How to calculate Latest month average?

Hi Sayali, thanks for the reply, but it is having a confidential data so sharing the work book is bit concern here, if you assist me with Generic way that should be fine.

Pls don't mind

• ###### 3. Re: How to calculate Latest month average?

Hi kalyan,

Can you tell us the data type of your Period field. I'm assuming it as a string field If so then you can try this.

Lets assume you've data like this

If Period field is a numeric then

DATEPARSE("yyMM",STR([Period]))={MAX(DATEPARSE("yyMM",STR([Period])))}

If its a string then

DATEPARSE("yyMM",[Period])={MAX(DATEPARSE("yyMM",[Period]))}

Once done then drag this to filters shelf and select only "True".

For Ratio of Actual/Total

Hope this help. Feel free to ask If you've any query.

Mahfooj

1 of 1 people found this helpful
• ###### 4. Re: How to calculate Latest month average?

Can you please share dummy data the way you have your data. Sorry for this, but i just need to understand your requirement clearly. If you can just do that.

Thank You

• ###### 5. Re: How to calculate Latest month average?

Hi Mahfooj, Thanks for the reply.

My period dimension datatype in String, when i try your calculation then it is givining error as below

• ###### 6. Re: How to calculate Latest month average?

My dimension is String datatype but having numeric values like 1605, 1606, 1607...1609.

I guess this will give you more clarity

• ###### 7. Re: How to calculate Latest month average?

Create calculations as below

Month:

RIGHT([Period],2)

Year:

LEFT([Period],2)

Date:

Date("01"+"/"+[Month]+"/"+[Year])

Will give you dd/mm/yyyy

Filter for max date

[Date]={MAX([Date])}

Thanks

• ###### 8. Re: How to calculate Latest month average?

Hi , Try this calculation.

INT([Period])={ FIXED : MAX(INT([Period]))}

drag this calculation into filters shelf and select True , so that it will give only data for the latest month. With out converting this string into date format you can use this to restrict data for latest month by just converting it into integer. As latest month will have highest number anyways in your string.

• ###### 9. Re: How to calculate Latest month average?

Which data source and what type of connection are you using? I guess you're connected live with your source.

DATEPARSE() function is available for non-legacy Microsoft Excel and text file connections, MySQL, Oracle, PostgreSQL, and Tableau data extract data sources. Some formats may not be available for all data sources.

You can use MAKEDATE() but it also works for data extracts. e;g

MAKEDATE(INT("20"+LEFT([Period],2)),INT(RIGHT([Period],2)),1) change the format as per requirement.

However you may try this using DATE() function.

DATE(RIGHT([Period],2)+"-01-20"+LEFT([Period],2))

Mahfooj

• ###### 10. Re: How to calculate Latest month average?

Thanks a lot Mahfooj, I have figured out using your help.

• ###### 11. Re: How to calculate Latest month average?

Thanks for your help Vardhan

• ###### 12. Re: How to calculate Latest month average?

Thanks Praveen

• ###### 13. Re: How to calculate Latest month average?

Thanks Sayali for a quick response, i have got the solution from other members suggestions

• ###### 14. Re: How to calculate Latest month average?

Try to close the thread If you got your answer by marking any of ours answers as correct which seems more relevant to you. Happy analysing