Hi i have seen your post for pivot for the rolling slicers for the month but how can we do it for the week if we have custom week start for the year. i.e Like my business year start for 2 July 17 and i want the rolling chart for the last 12 weeks. how can i do that please help. Thanks file is attached.
Hi Ahemed,
No file attached, but you just need to change the value 365 in the formula to 84, representing the number of days in 12 weeks, and modify the text:
=IF(today-[@Date]<84,"Last 12 Weeks",">12 Weeks")
Mynda
Hi mandy,
Thanks you very much for the reply however, one thing my date are start of the week dates and when i use the formula you have provided it does not work it only give me last then 12 week.
please see the attached file.
Regards
Hi Ahemed,
In the attached file your formula references cell S2, but this cell is empty so it's interpreted as 0 by the formula. Cell S2 should contain the date that you want to test against. e.g. today, yesterday, end of last week, whatever you want your last 12 weeks calculated from.
Also, the cell reference to S2 is not an absolute reference, so when you copy the formula down it doesn't stay fixed on S2.
Mynda
Hi Thanks for this could you please add the formula in it and send it to me, i will really appreciate this.
I am struggling to fix it.
Hi Ahemed,
In the attached file all of your dates were in the last 12 weeks so I changed the date in row 2 to show the formula works.
Mynda
Great thanks for your help all working.
