cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rakeshreddyp Helper I
Helper I

calculate average for last 6 months for multiple columns to calculate last 6 months variance

Hello everyone,
Need help with the below requirement
I have to calculate last 6 months variance for multiple metrics at column level to showcase on different reports.
I have created average for last 6 months for one column using below function

{AVG of Transport Cost = CALCULATE(SUM(WC_ULLT_KPI_SUMM_D_F[TOT_SALES_TON]),DATESINPERIOD(CALENDAR(WC_ULLT_KPI_SUMM_D_F[REP_DATE],LASTDATE(Calendar(WC_ULLT_KPI_SUMM_D_F[REP_DATE]),-6,MONTH))/6))}  and I am able to create it for one column. If I apply the same formula for second measure column it is showing circular dependency error.

In the next step I need to calculate the variance where I  am using the below formula for it. 

EX: June month varianvce=(Average of 6months (TOT_SALES_TON) - June(TOT_SALES_TON))/Average of 6months(TOT_SALES_TON)

likewise I have to calculate for 20 columns is there any way to acheive it?

2 REPLIES 2
Community Support
Community Support

Re: calculate average for last 6 months for multiple columns to calculate last 6 months variance

Hi rakeshreddyp,

In your scenario, you said you want to to get last 6 month 's variance, right? If the month is not last 6 months, will it show blank  or 0? If possible, could you please inform me more detailed information(such as your sample and expecting output)? Then I will help you more correctly.

Please do mask sensitive data before uploading.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

rakeshreddyp Helper I
Helper I

Re: calculate average for last 6 months for multiple columns to calculate last 6 months variance

Hi Zoe Zhi,

Sorry for the late response. 

I need to calculate the variance % for last 6months if the month has last 6 months data  otherwise, It will be the variance of the available months.

EX:

          sample.PNG

 

 for the Transport cost variance:

In case if the previous months has no data available the variance should sum up to the previous available months 

Variance(March)=Avg(Transport cost (Jan,Feb))-Transport cost(mar))/Avg(Transport cost (Jan,Feb))/ Variance (mar)=Avg(10,20)-10/15=0.33=33%

in case if the data is available

Var(sep)=Avg(Transport cost (Mar, Apr,May, june,jul,Aug))-Transport cost(sep))/Avg(Transport cost (Mar,Apr,May, june,jul,Aug))

Var(sep)= Avg(10,30,40,50,10,30)-20/ Avg(10,30,40,50,10,30)= .29 =29%

 

I hope this time I put it in a understandable way:)

 

Thanks,

Rakesh

 

 

Helpful resources

Announcements
Announcing the New Spanish Forum

Announcing the New Spanish Forum

Do you need help in Spanish? Check out our new Spanish community section.

MBAS Gallery 2020

MBAS Gallery 2020

Watch Microsoft Business Applications Summit sessions on-demand.

‘Better Together’ Integration Forum Launch

‘Better Together’ Integration Forum Launch

We've launched a how-to forum where you can learn about how Power BI integrates with other Power Platform products.

Top Solution Authors
Top Kudoed Authors