How will I calculate Avg YTD and Avg MAT

[mdaamirkhan]

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

[TomMartens]

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

[mdaamirkhan]

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?

[TomMartens]

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

[mdaamirkhan]

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

[mdaamirkhan]

PLease help me on this ?

[TomMartens]

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 

[mdaamirkhan]

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.

[TomMartens]

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

[mdaamirkhan]

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

[TomMartens]

Hey,

here you will find a little pbix file. 

The model contains a separate Calendar table, this table relates to your table by the calculated column I already mentioned earlier.

Your table now contains two measures

MAT Volume 12 = 
	var DateEnd = CALCULATE(MAX('Calendar'[Date]))
	var DateStart = MINX(DATEADD('Calendar'[Date],-12 + 1,MONTH),'Calendar'[Date])
	var theDatesBetween = DATESBETWEEN('Calendar'[Date], DateStart,DateEnd)
	return
IF(NOT(ISBLANK(CALCULATE(SUM(Milupa_Input[VOLUME])))),
	CALCULATE(
		SUM('Milupa_Input'[VOLUME])
		,theDatesBetween
	)
)

and

MAT Volume AVG 12 = 
	var DateEnd = CALCULATE(MAX('Calendar'[Date]))
	var DateStart = CALCULATE(MINX(DATEADD('Calendar'[Date],-12 + 1,MONTH),'Calendar'[Date]))
	var theDatesFromFact = DATESBETWEEN('Milupa_Input'[Date],DateStart,DateEnd)
	var theNoOfMonths =
		COUNTROWS(
				SUMMARIZE(
					theDatesFromFact
					,'Milupa_Input'[Date]
				,"MonthNo", Month(MAX('Milupa_Input'[Date]))			
				)
				
			)
		
	return
IF(NOT(ISBLANK(CALCULATE(SUM(Milupa_Input[VOLUME])))),
		[MAT Volume 12] / theNoOfMonths
	)

The second measure calculates the Moving Annual Average, be aware that the first month July 2014 is not divided by 12 instead by 1, the 2nd by two. Only when 12 previous months are available, the MAT is divided by 12.

For your six months measures just create 2 new measures and just change the part where I go 12 months to 6 months.

I guess this should get you started

Regards

Tom

[mdaamirkhan]

For 6 months below query is ok or not. Anything changes needed in the query.

MAT Volume 6 = 

var DateEnd = CALCULATE(MAX('Calendar'[Date]))

var DateStart = MINX(DATEADD('Calendar'[Date],-6 + 1,MONTH),'Calendar'[Date])

var theDatesBetween = DATESBETWEEN('Calendar'[Date], DateStart,DateEnd)

return

IF(NOT(ISBLANK(CALCULATE(SUM(Milupa_Input[VOLUME])))),

CALCULATE(

SUM('Milupa_Input'[VOLUME])

,theDatesBetween

)

)

And

MAT Volume AVG 6 = 

var DateEnd = CALCULATE(MAX('Calendar'[Date]))

var DateStart = CALCULATE(MINX(DATEADD('Calendar'[Date],-6 + 1,MONTH),'Calendar'[Date]))

var theDatesFromFact = DATESBETWEEN('Milupa_Input'[Date],DateStart,DateEnd)

var theNoOfMonths =

COUNTROWS(

SUMMARIZE(

theDatesFromFact

,'Milupa_Input'[Date]

,"MonthNo", Month(MAX('Milupa_Input'[Date]))

)

)

return

IF(NOT(ISBLANK(CALCULATE(SUM(Milupa_Input[VOLUME])))),

[MAT Volume 6] / theNoOfMonths

)

[TomMartens]

Looks good

[mdaamirkhan]

In your previous query for AVG YTD 

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
		)
	)

How will I calculate for 12 months and also 6 month ?  I cannot find/understand where I will modify the query ?

[TomMartens]

Hey,

I'm not sure what you are trying to achieve, my first measure did not consider the moving frame and crossing years. The 1st measure just calculates a year to date value.

I recommend that you get yourself accustomed with time calculations using DAX, for this reason have a closer look at this great site:

http://www.daxpatterns.com/time-patterns/

Regards

Tom

[mdaamirkhan]

ok. As you have provide MAT AVG For 12 months and I have for MAT AVG For 6 monthsbelow query is ok or not. Anything changes needed in the query. For 12 months and 6 month values has are showing same which is not not correct. It should be different when I am calculating for 6 months which is not. Can you please have a look .

MAT Volume 6 = 

var DateEnd = CALCULATE(MAX('Calendar'[Date]))

var DateStart = MINX(DATEADD('Calendar'[Date],-6 + 1,MONTH),'Calendar'[Date])

var theDatesBetween = DATESBETWEEN('Calendar'[Date], DateStart,DateEnd)

return

IF(NOT(ISBLANK(CALCULATE(SUM(Milupa_Input[VOLUME])))),

CALCULATE(

SUM('Milupa_Input'[VOLUME])

,theDatesBetween

)

)

And

MAT Volume AVG 6 = 

var DateEnd = CALCULATE(MAX('Calendar'[Date]))

var DateStart = CALCULATE(MINX(DATEADD('Calendar'[Date],-6 + 1,MONTH),'Calendar'[Date]))

var theDatesFromFact = DATESBETWEEN('Milupa_Input'[Date],DateStart,DateEnd)

var theNoOfMonths =

COUNTROWS(

SUMMARIZE(

theDatesFromFact

,'Milupa_Input'[Date]

,"MonthNo", Month(MAX('Milupa_Input'[Date]))

)

)

return

IF(NOT(ISBLANK(CALCULATE(SUM(Milupa_Input[VOLUME])))),

[MAT Volume 6] / theNoOfMonths

)

There should be difference between 6 months and 12 months which is not showing

[TomMartens · Accepted solution]

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

[mdaamirkhan]

Thanks a lot for helping me