Date Grouping based on MOST RECENT dates

[ryan_b_fiting]

Hello Community - 

I am looking to create a 4 week grouping of data that is based off of the most recent date.  By that I mean, I am looking to have 4 week groupings, but with the most recent 'group' ALWAYS having 4 weeks in it.  So if you take a look at the sample of data below the first 4 periods (4/1/20 thru 4/22/20) would be in a 4 week ended bucket of 4/22/20.  However, when I get new data for the week ended 4/29/20, I would want the weeks of 4/8/20 thru 4/29/20 to now be groups as a 4 Week ended 4/29/20. (Obviously as a single column that changes whenever data is added).

I know I can create groups/bins but those based off of the earliest date and when new data is added, it is sometimes only 1, 2 or 3 weeks worth of data because of the way the bins are calculated from the earliest date in the calendar table.

Period	Brand	Sales	4 Week Ended	New Data 4 Wk Ended
4/1/20	ABC	1234	4/22/20	4/1/20
4/8/20	ABC	2345	4/22/20	4/29/20
4/15/20	ABC	3455	4/22/20	4/29/20
4/22/20	ABC	4567	4/22/20	4/29/20
4/29/20	ABC	4563		4/29/20

Does anyone in the community know how to create this type of binning.  One that is dynamic and is always creating the 4 week bin based on the most recent date?

Thanks in advance for any help you can provide!

Ryan

[v-jayw-msft]

Hi @ryan_b_fiting ,

Has the problem be solved? Please consider to mark the answer as solution if it works for you.

Best Regards,

Jay

[ryan_b_fiting]

@v-jayw-msft no this has not been solved.  I have tried all the suggestions and a few others as well, but no luck yet.

[Ashish_Mathur]

Hi,

This calculated column formula seems to work

=CALCULATE(MAX(Table1[Period]),FILTER(Table1,Table1[Brand]=EARLIER(Table1[Brand])&&Table1[Period]<=EARLIER(Table1[Period])+21))

Hope this helps.

[DataInsights · Accepted solution]

@ryan_b_fiting,

Try this calculated column:

4 Weeks Ended = 
VAR vMaxDate =
    MAX ( Sales[Period] )
VAR vDaysDiff =
    CONVERT ( vMaxDate - Sales[Period], INTEGER )
VAR vDelta =
    ROUNDDOWN ( DIVIDE ( vDaysDiff, 28 ), 0 )
VAR vResult = vMaxDate - ( vDelta * 28 )
RETURN
    vResult

[ryan_b_fiting]

@DataInsights this one does work for almost everything I need.  The only time it will not work is if we are selecting a specified date range that does not include the most recent period of data.  For example if we have data through November 1, 2020.  But I wanted to look to see what my numbers were for 2019 through the same week number in November, this will not dynamically adjust the groupings based on that.

Other than that, this solves everything I asked for, so I am marking it as the solution.

Thanks a ton for your help @DataInsights 

[DataInsights]

@ryan_b_fiting,

Try this measure. It requires the following:

1. Slicer based on the Date table.

2. A relationship between the Sales and Date tables.

4 Weeks Ended Measure = 
VAR vMaxDate =
    LASTDATE ( ALLSELECTED ( 'Date'[Date] ) )
VAR vCurrentDate =
    MAX ( Sales[Period] )
VAR vDaysDiff =
    CONVERT ( vMaxDate - vCurrentDate, INTEGER )
VAR vDelta =
    ROUNDDOWN ( DIVIDE ( vDaysDiff, 28 ), 0 )
VAR vResult = vMaxDate - ( vDelta * 28 )
RETURN
    IF ( ISBLANK ( vCurrentDate ), BLANK (), vResult )

[DataInsights]

@ryan_b_fiting,

Try this measure:

4 Weeks Ended = 
VAR vMaxDate =
    CALCULATE ( MAX ( Sales[Period] ), ALL ( Sales ) )
VAR vStartDate =
    vMaxDate - 28 --subtract 4 weeks
VAR vRowDate =
    MAX ( Sales[Period] )
VAR vResult =
    IF ( vRowDate > vStartDate, vMaxDate, vRowDate )
RETURN
    vResult

[ryan_b_fiting]

@DataInsights this does give me the the most recent grouping of 4 weeks it appears, but after that it is each individual date.  The groupings would need to remain in 4 week increments, just by dynamically changing as new data is added for the week.

So with the example I provided, when I add 3 more weeks worth of data I would expect to see this:

Brand	Period	Sales	4 Weeks Ended
ABC	4/1/20	1234	4/22/20
ABC	4/8/20	2345	4/22/20
ABC	4/15/20	3455	4/22/20
ABC	4/22/20	4567	4/22/20
ABC	4/29/20	4563	5/20/20
ABC	5/6/20	7654	5/20/20
ABC	5/13/20	4567	5/20/20
ABC	5/20/20	8875	5/20/20

So it always needs to be a rolling 4 week grouping.  We are usually using a date range of the last 1-2 years.

I think a few of the variables need to get adjusted, but I cannot figure out to what!

If you have any other ideas on how to do this, I am all ears!  I have been trying to figure this out for a while now.

Thanks!