- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

# How will I calculate Avg YTD and Avg MAT

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-06-2017 02:43 AM

Can you please help me How will I calculate Avg YTD and Avg MAT based on Brand?

I have share pbix file link : https://drive.google.com/open?id=0BxreilNC4AvsVGV6dFk0R0RfMGM

It will be very helpful if can help me out I am stuck

Thanks

Aamir

Solved! Go to Solution.

Accepted Solutions

## Re: How will I calculate Avg YTD and Avg MAT

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-08-2017 02:19 AM

Hey,

not sure, what you are looking at, the first 6 months of your timeframe would of course show the same value.

Please have a look at this picture, and you also may to redownload my pbix file that now also contains the measures for the 6 months period:

Regards

Tom

If I answer you question, please mark my post as solution, this will also help others.

Proud to be a Datanaut!

All Replies

## Re: How will I calculate Avg YTD and Avg MAT

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-06-2017 03:59 AM

Hey,

thanks for uploading a sample pbix.

First I created a calculated column representing a date column in your table using this DAX statement

Date = Date('Milupa_Input'[YEAR],'Milupa_Input'[MONTH],1)

Maybe this DAX statement to create a Measure gets you started

Average Volume YTD = var currentYear = YEAR(MAX('Milupa_Input'[Date])) var currentMonth = MONTH(MAX('Milupa_Input'[Date])) return CALCULATE( AVERAGE('Milupa_Input'[VOLUME]) ,FILTER(ALL('Milupa_Input'[Date]) ,YEAR('Milupa_Input'[Date]) = currentYear && Month('Milupa_Input'[Date]) <= currentMonth ) )

But I realized that your table contains a lot of zero values and also negative values, this may lead to an unexpected result.

Maybe this gets you started

Regards

Tom

If I answer you question, please mark my post as solution, this will also help others.

Proud to be a Datanaut!

## Re: How will I calculate Avg YTD and Avg MAT

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-06-2017 04:15 AM

If I use Period instead Month will it be also ok or its wrong. You have calculeted Avg Volume YTD what will be the Avg Volume MAT? How will I calculate Avg MAT?

## Re: How will I calculate Avg YTD and Avg MAT

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-06-2017 04:35 AM

Hey,

from your sample data I have no idea what period means and also not what MAT means.

If MAT is just a missing column in your sample data, you just have create a measure and replace Volume with MAT.

If MAT is a measure, things can become a little more complex due to the nature of the calculation.

Regards

Tom

If I answer you question, please mark my post as solution, this will also help others.

Proud to be a Datanaut!

## Re: How will I calculate Avg YTD and Avg MAT

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-06-2017 05:11 AM - edited 09-06-2017 07:00 AM

If you tell which column is missing my sample data and if you give me e.g- it will be better and if you also provide me th Avg MAT calculation it will be helpful.

And how will I create below screenshot calculation: I have also shared following report which I want to create link: https://drive.google.com/open?id=0BxreilNC4Avsb2pDb1l1UW1xNzQ

## Re: How will I calculate Avg YTD and Avg MAT

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-06-2017 08:42 PM

PLease help me on this ?

## Re: How will I calculate Avg YTD and Avg MAT

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-07-2017 04:33 AM

Hey,

in your initial post, linking to your pbix file, you ask how to calculate the Average MAT YTD. From the pbix I have no idea what MAT stands for or how to calculate this, given the information in the pbix file.

Later on you ask if you can apply my DAX statement to period, once again, i have no idea what you mean by period, a mont, or a timeframe selected by a user using a slicer.

For this reason, I stated that the pbix is missing some information. The sheet you provided, does not really answer my questions, please provide another pbix if necessary that contains all the necessay data to calculate all the things you need.

Regards

Tom

If I answer you question, please mark my post as solution, this will also help others.

Proud to be a Datanaut!

## Re: How will I calculate Avg YTD and Avg MAT

[ Edited ]- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-07-2017 04:43 AM - edited 09-07-2017 04:47 AM

You said previously that

If MAT is just a missing column in your sample data, you just have create a measure and replace Volume with MAT. ok

If MAT is a measure, things can become a little more complex due to the nature of the calculation. can you share the complex query on that of Mat Avg. Mat means (Moving Annual Total).

And in the below screenshot YTD is calculating with 6 period month. How will calculation AVG YTD with 6 period month.

## Re: How will I calculate Avg YTD and Avg MAT

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-07-2017 04:54 AM

Okay,

now I understand, what MAT means. But still there are questions.

Assume I want to calculate the MAT for VOLUME, what is the moving part, is it the last 12 months: For February 2015, this means calculate the MAT using the values from March 2014 to February 2015 and the Average MAT is just the average for these 12 months.

Regards

Tom

If I answer you question, please mark my post as solution, this will also help others.

Proud to be a Datanaut!

## Re: How will I calculate Avg YTD and Avg MAT

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

09-07-2017 05:00 AM

Yes you are rite. But I need two calculation one for 12 months and for 6 months.

And How I will calculate AVG YTD for last 6 month