I have 2 columns in my sheet sales amount and weekending date. I need to calculate sales amount for last 4 weeks, 12 weeks and 26 weeks and compare the same with previou year last 4 weeks, 12 weeks and 26 weeks. I tried few ways looking at similar posts before and looks like it is not working. I need to calculate it based on weeknumber because the iso week number for a given date this year is different to previous year same date value. Attaching the excel and pbix files. So for weekending 2/25/18 last 4 week sales amount should be 275 and last 12 weeks is 780 and Last 4 weeks previous year is 280. Thanks in advance for the help.
0
votes
1 Answers
0
votes
First create a simple "Sum" measure like so,
Sum Of Sales Amount = Sum(Table[Sales Amount])
Next, you will need to create several measure for each period using the following format:
Sales Amount 4 Weeks = CALCULATE([Sum Of Sales Amount], DATESINPERIOD(Table[Week Ending Date], LASTDATE(Table[Week Ending Date]),-28,DAY))
Since we don't have a "WEEK" parameter available for the interval in the DATESINPERIOD function, I just took the product of 7 and 4 i.e. 4 weeks.
This will work for any periods within the current year.
I hope this helps!
