topic Re: Trended Weeks over Prior Period Help in DAX Commands and Tips

Trended Weeks over Prior Period Help

AndrewUrban — Sat, 25 Jun 2022 14:05:22 GMT

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

Re: Trended Weeks over Prior Period Help

ManguilibeKAO — Sat, 25 Jun 2022 21:18:20 GMT

Hi AndrewUrban,

Coud you show the tables in your data model?

Best regards.

Re: Trended Weeks over Prior Period Help

AndrewUrban — Mon, 27 Jun 2022 13:25:32 GMT

Hi,

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

Re: Trended Weeks over Prior Period Help

v-jianboli-msft — Tue, 28 Jun 2022 06:37:55 GMT

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.

Re: Trended Weeks over Prior Period Help

AndrewUrban — Tue, 28 Jun 2022 14:49:20 GMT

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