Forum Discussion

ryan_b_fiting's avatar
ryan_b_fiting
Icon for Post Patron rankPost Patron
5 years ago
Solved

Date 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...
Interesting Links
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • DataInsights's avatar
    DataInsights
    5 years ago

    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

     

     

Ashish_Mathur's avatar
Ashish_Mathur
Icon for Super User rankSuper User
5 years ago

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's avatar
DataInsights
Icon for Super User rankSuper User
5 years ago

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's avatar
    ryan_b_fiting
    Icon for Post Patron rankPost Patron
    5 years ago

    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's avatar
      DataInsights
      Icon for Super User rankSuper User
      5 years ago

      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 )