Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
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
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 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?
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 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
PLease help me on this ?
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
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.
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
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
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
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
)
Looks good
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 ?
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
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
)
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
Thanks a lot for helping me
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |