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 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_fitingPost Patron
Post PatronDataInsights 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
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 )