Forum Discussion
ryan_b_fiting
Post Patron
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...
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
DataInsights
Super User
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_fitingPost Patron
Post PatronDataInsights 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!