Forum Discussion

amaddala's avatar
amaddala
Icon for Helper I rankHelper I
5 years ago

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...
Need Help
Anonymous's avatar
Anonymous
Not applicable
5 years ago

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”

 

 

  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:

 

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.

amaddala's avatar
amaddala
Icon for Helper I rankHelper I
5 years ago

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

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.

     

    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.

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

     

    Best Regards,

    Yuna

    • amaddala's avatar
      amaddala
      Icon for Helper I rankHelper I
      5 years ago

      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. 

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        Hi amaddala ,

         

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

         

        Best Regards,

        Yuna