Previous year sales for the same weeks

[campos_rsc]

Hi everyone,

I have a bit of a challenge with the following scenario:

Data set is aggregated at a WeekStart Level (lowest level) - I'm deriving the month and year from this date. The user wants to select Year and Month, let's say 2020 - May and see the Sales for last year - same weeks by Product. 

So if May 2020 has 5 weeks by WeekStart I would want to show the Sales for the same weeks previous year irrespective of the fact that one of the weeks might fall into June 2019. Remember that only one Year is always selected and Month has one or several months always selected and the final table is made of Product, Sales this Period and Sales Previous Year.

Hope it makes sense, any help would be greatly appreciated.

Thanks

Raz

[usamabinsadiq]

Hi Raz,

if you can provide some example data then I think I might be able to help with this.

[campos_rsc]

Please find attached. 

So again, for the previous year I don't want the Week 18 to be included in the calculation when selecting May 2020.

Thanks

[usamabinsadiq · Accepted solution]

Hi

Please see the result, I think you were looking for this:

Measure Expressions:

//Sales this Period
Sum({<Year={$(=max(Year))}>} Sales)

//Sales Previous Year
Sum({<Year={$(=max(Year)-1)},Week={"$(= '<=' & max(Week) & '>=' & min(Week) )"}>} Sales)

hope this helps,

Regards,

[campos_rsc]

Perfect, works as expected.

Thank you, much appreciated.