Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Calculate Non-Rolling 13 Week SUM

Hello, I'm trying to find a formula that will calculate a static, non-rolling, SUM of the previous 13 weeks of ordered units. Once I can SUM the last 13 weeks of the Ordered and Confirmed Quantities...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    7 years ago

    Hi Anonymous ,

     

    Your formula works fine with the filter on my side, this formula had considered the outer slicers.

     

    Non-rolling 13 WK Confirmation Rate = 
Var t = 
    ADDCOLUMNS (
        ALLSELECTED( 'Table'),
        "WeekIndex", RANKX ( ALLSELECTED ( 'Table' ),[Week of Year],, DESC )
        )
Var i =
    MAXX (
        FILTER ( t , 'Table' [Week of Year] = MAX ('Table' [Week of Year] ) ),
        [WeekIndex] 
        )
RETURN
    CALCULATE (
        SUM ('Table'[Confirmed Quantity] ),
        FILTER ( t, [WeekIndex] <= i + 12 && [WeekIndex] >= i )
        )
        / CALCULATE (
            SUM ('Table'[Ordered Qty] ),
            FILTER ( t, [WeekIndex] <= i + 12 && [WeekIndex] >= i )
        )

     

     


    BTW, pbix as attached.

     

    Best regards,

    Community Support Team _ Dong Li
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi v-lid-msft ,

 

Unfortunately your formula in the original and secondary post have not brought me to the same solution as you got in your .pbix file. I'm not sure what else could be the cause of my formula not working.. given that I copied every detail of your formula over to my own file.. I'm not sure what else could be wrong. Could you please walk me through the logic behind how you came to this formula as the answer? I don't understand how RANKX, ADDCOLUMNS, and ALLSELECTED are used to find the result.

 

Perhaps there's a different way to write the formula that I could try? apologies for the lack of detail on how to move forward..

Thanks,

Kordel

v-lid-msft's avatar
v-lid-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi Anonymous ,


How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.