Forum Discussion
Post PatronDate Grouping based on MOST RECENT dates
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
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
8 Replies
- DataInsights
Super User
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_fitingDataInsights 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!
- 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
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
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
Anonymous no this has not been solved. I have tried all the suggestions and a few others as well, but no luck yet.
