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

Moving average with custom calendar

I need help calculating moving average of last 4 fiscal weeks in my data. We cannot use datesinperiod as I need to use "Week" in my steps. I have fiscalweek number ( for ex: 31, 32, 33 etc ) 

Please see excel version in the attached image. 

 

MA.png

11 REPLIES 11
Microsoft
Microsoft

Hi @amaddala ,

Based on your description, you can do some steps as follows.

  1. Create a rank column in power query.

Duplicate the “fiscal week” column-->split it by number of characters(left 6)-->remove the unnecessary column and rename the necessary one as “week”

v-yuaj-msft_0-1611641489446.png

 

v-yuaj-msft_1-1611641489449.png

 

  1. Create a measure.

 

Measure = IF(MAX('Sheet1'[week])<=4,0,AVERAGEX(FILTER(ALL(Sheet1),[week]<SELECTEDVALUE(Sheet1[week])&&[week]>=SELECTEDVALUE(Sheet1[week])-4),[Transaction#]))

 

Result:

v-yuaj-msft_2-1611641489457.png

 

Hope that's what you were looking for.

Best Regards,

Yuna

 

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

Your idea was helpful, but when I tried to apply this the result wasnt coming correctly. However, I created a new dax as below. It worked, but not sure what was the difference between your logic and mine. 

But when I try to add any other dimension to this, the value remains the same. 

 

6WK_MA_Transactions =
var WK_1 = SUMX(FILTER(
ALLSELECTED(Summaryold),
[fiscalweeknumber]=SELECTEDVALUE(Summaryold[fiscalweeknumber])-1
), Summaryold[Transaction#])
var WK_2 = SUMX(FILTER(
ALLSELECTED(Summaryold),
[fiscalweeknumber]=SELECTEDVALUE(Summaryold[fiscalweeknumber])-2
), Summaryold[Transaction#])
var WK_3 = SUMX(FILTER(
ALLSELECTED(Summaryold),
[fiscalweeknumber]=SELECTEDVALUE(Summaryold[fiscalweeknumber])-3
), Summaryold[Transaction#])
var WK_4 = SUMX(FILTER(
ALLSELECTED(Summaryold),
[fiscalweeknumber]=SELECTEDVALUE(Summaryold[fiscalweeknumber])-4
), Summaryold[Transaction#])
var WK_5 = SUMX(FILTER(
ALLSELECTED(Summaryold),
[fiscalweeknumber]=SELECTEDVALUE(Summaryold[fiscalweeknumber])-5
), Summaryold[Transaction#])
var WK_6 = SUMX(FILTER(
ALLSELECTED(Summaryold),
[fiscalweeknumber]=SELECTEDVALUE(Summaryold[fiscalweeknumber])-4
), Summaryold[Transaction#])
return (WK_1 + WK_2+ WK_3 + WK_4 + wk_5 + wk_6)/6

 

Hi @amaddala ,

 

Could you please tell me what went wrong? Could you share some screenshots around the issue please? This is not helping much.

Add error screenshot as well which you are facing. 

 

I tested it again with new data, and there were no errors.

v-yuaj-msft_0-1611885368852.png

 

You said that you want to calculate the moving average of the previous four weeks, but the formula you gave is the moving average of the previous six weeks.

v-yuaj-msft_1-1611885570472.png

Maybe I'm not getting what actually you are trying to do.

 

Best Regards,

Yuna

Thank you, I have just used 6 weeks instead of 4 with the same logic you shared as my business requirement changed. Below is the screenshot for what I am seeing with my code vs yours. 

 

Screenshot 2021-01-29 123555.png

 

Hi @amaddala ,

 

Are there blank values in your column “Transaction#”?

 

Best Regards,

Yuna

No, there arent any blank values for transaction#. 

Hi @amaddala ,

 

v-yuaj-msft_1-1611909658288.png

It seems that nothing went wrong.

Can you share some sample data and the expected result to have a clear understanding of your question? I can do some tests for you.

You can save your files in OneDrive, Google Drive, or any other cloud sharing platforms and share the link here.

 

Best Regards,

Yuna

Microsoft
Microsoft

Hi @amaddala ,

 

Based on your description, I did a test. Is the following result what you want?

 

v-yuaj-msft_0-1611538566092.png

 

Best Regards,

Yuna

Yes, this is what I am looking for. How did you achieve this?

Helper I
Helper I

I am having trouble creating rank in the first place. I am getting the below error. 

 

A single value for column 'fiscalweeknumber' in table 'Summaryold' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result.

Super User IV
Super User IV

@amaddala , this way week is sortable, hope you have W01.

Create a Rank on this , a new column

Week Rank = RANKX(all('Date'),'Date'[FiscalWeek],,ASC,Dense) //YYYYWW format

 

Try these measures - example measures

 

This Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])))
Last Week = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=max('Date'[Week Rank])-1))
Last year Week= CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]=(max('Date'[Week Rank]) -52)))
Last 4 weeks = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-4 && 'Date'[Week Rank]<=max('Date'[Week Rank])))



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
Dashboard of My Blogs !! YouTube Channel !! Connect on Linkedin

Proud to be a Super User!

Helpful resources

Announcements
secondImage

Congratulations!

We are excited to announce the Power BI Super Users!

Wave Release 2

Check out the updates in Power BI.

Overview of Power BI 2020 release wave 2!

Microsoft Ignite

Microsoft Ignite

Join digitally, March 2–4, 2021 to explore new tech that's ready to implement. Experience the keynote in mixed reality through AltspaceVR!

secondImage

The largest Power BI virtual conference

100+ sessions, 100+ speakers, Product managers, MVPs, and experts. All about Power BI. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors