Trended Weeks over Prior Period Help

[AndrewUrban]

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!!

[v-jianboli-msft]

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 · Accepted solution]

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

[ManguilibeKAO]

Hi AndrewUrban,

Coud you show the tables in your data model?

Best regards.

[AndrewUrban]

Hi,

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