Date Groupings - 4 Week Increments

[ryan_b_fiting]

Hello Community - 

I have weekly data that has a report date for each week ending date.  I want to create groupings so that I can have the values aggregated into 4 week buckets.  I have provided a sample set of data below.  I have a date_table created and joined to my fact table already, just looking for the best solution on bucketing the dates into 4 wk increments.  The column below labeled '4 wk Ended' would be the desired result for this sample data.

Thank you in advance for your help!

Ryan F

Product	Time	Dollar Sales	Unit Sales	Dollars per Store Selling	ACV Weighted Distribution	4 Wk Ended
ICE CREAM/SHERBET-AISLE-FROZEN DESSERTS	1/14/2018 0:00	203328463.7	53454628.8	226159.7235	6363.464386	2/4/2018
ICE CREAM/SHERBET-AISLE-FROZEN DESSERTS	1/21/2018 0:00	207178405.3	54684045.36	228142.8372	6363.652691	2/4/2018
ICE CREAM/SHERBET-AISLE-FROZEN DESSERTS	1/28/2018 0:00	206635242.9	54192502.4	232659.2505	6363.416276	2/4/2018
ICE CREAM/SHERBET-AISLE-FROZEN DESSERTS	2/4/2018 0:00	221548897	59375332.96	255433.9748	6363.422995	2/4/2018
ICE CREAM/SHERBET-AISLE-FROZEN DESSERTS	2/11/2018 0:00	218061850	58089635.75	241292.2978	6363.746624	3/4/2018
ICE CREAM/SHERBET-AISLE-FROZEN DESSERTS	2/18/2018 0:00	210621457.4	55292082.98	235882.6835	6364.94967	3/4/2018
ICE CREAM/SHERBET-AISLE-FROZEN DESSERTS	2/25/2018 0:00	210797196.8	55520038.58	233594.5362	6365.43007	3/4/2018
ICE CREAM/SHERBET-AISLE-FROZEN DESSERTS	3/4/2018 0:00	218970225.8	58214272.92	250562.4547	6364.587717	3/4/2018
ICE CREAM/SHERBET-AISLE-FROZEN DESSERTS	3/11/2018 0:00	225696408.2	59765791.72	253185.8444	6364.864355	4/1/2018
ICE CREAM/SHERBET-AISLE-FROZEN DESSERTS	3/18/2018 0:00	226014643.5	59344004.75	253957.6583	6365.181209	4/1/2018
ICE CREAM/SHERBET-AISLE-FROZEN DESSERTS	3/25/2018 0:00	224440533.3	59573042.64	249459.7172	6366.149926	4/1/2018
ICE CREAM/SHERBET-AISLE-FROZEN DESSERTS	4/1/2018 0:00	234939413.2	63234485.76	267739.3351	6366.18446	4/1/2018

[ryan_b_fiting]

Hi - 

Thanks for the suggestions everyone.  However, I do not think I explained my situation properly.

With my example below, what I will need to happen is when I add a new week of data (4/8/2018 in this example), all the 4 week periods shift so that the most recent always has 4 weeks bucketed into it.

So if I added the 4/8/2018 data the buckets should be reflected as this:

Time	4 Wk Ended
1/14/2018	1/14/2018
1/21/2018	2/11/2018
1/28/2018	2/11/2018
2/4/2018	2/11/2018
2/11/2018	2/11/2018
2/18/2018	3/11/2018
2/25/2018	3/11/2018
3/4/2018	3/11/2018
3/11/2018	3/11/2018
3/18/2018	4/8/2018
3/25/2018	4/8/2018
4/1/2018	4/8/2018
4/8/2018	4/8/2018

I cannot get that to happen with the solutions below.  Are there any suggestions to get this to work?

Thanks in advance for all the help!

[v-kelly-msft · Accepted solution]

Hi  @ryan_b_fiting ,

First go to query editor >create an index column;

Then create 2 columns as below:

rankx = RANKX(FILTER('Table',MOD('Table'[Index],4)=0),'Table'[Index],,ASC)

4 Wk Ended = 
var _mindate=CALCULATE(MIN('Table'[Time]),FILTER('Table','Table'[rankx]=EARLIER('Table'[rankx])))
Return
_mindate+21

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[cyclist007]

Have you considered right clicking on the column and making a new group with bin size as 28 days

[amitchandak]

@ryan_b_fiting , You can create a date table, and there you can create week start and week end is +28 days.

and create a bucket based on that.

example , new columns in date table

Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = [Week Start date] +4
Week = if('Date'[Week Number]<10,'Date'[Year]*10 & 'Date'[Week Number],'Date'[Year]&'Date'[Week Number])

of you can get on the week start date and create 4-week period post that

Min Week Start date = min([Week Start date])

4 Week No = Quotient(datediff([Min Week Start date],date,day),28)+1