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_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!