Forum Discussion

AndrewUrban's avatar
AndrewUrban
Frequent Visitor
4 years ago
Solved

Trended Weeks over Prior Period Help

Hello! 

 

I'm hoping someone can help me write a DAX statement for a prior period (latest 12, 26, 52 weeks) which I'd like to then turn into a % chg. I have weekly data now which i've created trended times for, but I don't know how to get the prior periods trended time.

 

Below is one of my current formula's for latest 24wks periods:

 

Latest 24 Wks = CALCULATE(sum(Kroger[Units]), FILTER(ALL('Date'),'Date'[Week Rank]>=max('Date'[Week Rank])-23 && 'Date'[Week Rank]<=max('Date'[Week Rank])))
 
Thanks for your help!!
  • AndrewUrban's avatar
    AndrewUrban
    4 years ago

    This is fantastic! I think I can figure out the rest from here, thank you so much for breaking this down. 

     

4 Replies

  • ManguilibeKAO's avatar
    ManguilibeKAO
    Icon for Resolver I rankResolver I
    4 years ago

    Hi AndrewUrban,

     

    Coud you show the tables in your data model?

     

    Best regards.

    • AndrewUrban's avatar
      AndrewUrban
      Frequent Visitor
      4 years ago

      Hi,

       

      Thanks for the reply, see below for my "Date" table:

       

       

       

       

       

       

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi AndrewUrban ,

     

    Please follow these steps:

    1. Create a new table with all the options you need in the slicer.

    2. Turn the single select of the slicer on

    3. Then create a measure that calculate a prior period.

     

     

    Measure =

SWITCH( MAX('for sliceer'[Value]),

"Last 12 Weeks",

CALCULATE(SUM(Kroger[Units]),FILTER(ALL('Date'),'Date'[Week Rank] >= MAX('Date'[Week Rank])-11 && 'Date'[Week Rank] <= MAX('Date'[Week Rank]))),

"Last 26 Weeks",

CALCULATE(SUM(Kroger[Units]),FILTER(ALL('Date'),'Date'[Week Rank] >= MAX('Date'[Week Rank])-25 && 'Date'[Week Rank] <= MAX('Date'[Week Rank]))),

CALCULATE(SUM(Kroger[Units]),FILTER(ALL('Date'),'Date'[Week Rank] >= MAX('Date'[Week Rank])-51 && 'Date'[Week Rank] <= MAX('Date'[Week Rank])))

)

     

    4. Final output

     

     

     

    In addition, what's the logic of "a % chg", please provide more details to help us clarify your scenario.

     

    Best Regards,

    Jianbo Li

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

    • AndrewUrban's avatar
      AndrewUrban
      Frequent Visitor
      4 years ago

      This is fantastic! I think I can figure out the rest from here, thank you so much for breaking this down.