Reply
Member
Posts: 208
Registered: ‎07-06-2017
Accepted Solution

How will I calculate Avg YTD and Avg MAT

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


Accepted Solutions
Super User
Posts: 1,439
Registered: ‎06-24-2015

Re: How will I calculate Avg YTD and Avg MAT

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:

2017-09-08_11-12-42.png

Regards

Tom

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!

View solution in original post


All Replies
Super User
Posts: 1,439
Registered: ‎06-24-2015

Re: How will I calculate Avg YTD and Avg MAT

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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Member
Posts: 208
Registered: ‎07-06-2017

Re: How will I calculate Avg YTD and Avg MAT

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?

Super User
Posts: 1,439
Registered: ‎06-24-2015

Re: How will I calculate Avg YTD and Avg MAT

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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Member
Posts: 208
Registered: ‎07-06-2017

Re: How will I calculate Avg YTD and Avg MAT

[ Edited ]

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

Capture.PNG

 

 

Member
Posts: 208
Registered: ‎07-06-2017

Re: How will I calculate Avg YTD and Avg MAT

PLease help me on this ?

Super User
Posts: 1,439
Registered: ‎06-24-2015

Re: How will I calculate Avg YTD and Avg MAT

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 

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Member
Posts: 208
Registered: ‎07-06-2017

Re: How will I calculate Avg YTD and Avg MAT

[ Edited ]

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.

Screenshot 2017-09-07 at 05.44.26 PM.png

 

 

Super User
Posts: 1,439
Registered: ‎06-24-2015

Re: How will I calculate Avg YTD and Avg MAT

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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Member
Posts: 208
Registered: ‎07-06-2017

Re: How will I calculate Avg YTD and Avg MAT

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