Forum Discussion

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

Date Groupings - 4 Week Increments

Hello Community - 

I have weekly data that has a report date for each week ending date.  I want to create groupings so that I can have the values aggregated into 4 week buckets.  I have provided a sample set of data below.  I have a date_table created and joined to my fact table already, just looking for the best solution on bucketing the dates into 4 wk increments.  The column below labeled '4 wk Ended' would be the desired result for this sample data.

Thank you in advance for your help!

Ryan F

 

ProductTimeDollar SalesUnit SalesDollars per Store SellingACV Weighted Distribution4 Wk Ended
ICE CREAM/SHERBET-AISLE-FROZEN DESSERTS1/14/2018 0:00203328463.753454628.8226159.72356363.4643862/4/2018
ICE CREAM/SHERBET-AISLE-FROZEN DESSERTS1/21/2018 0:00207178405.354684045.36228142.83726363.6526912/4/2018
ICE CREAM/SHERBET-AISLE-FROZEN DESSERTS1/28/2018 0:00206635242.954192502.4232659.25056363.4162762/4/2018
ICE CREAM/SHERBET-AISLE-FROZEN DESSERTS2/4/2018 0:0022154889759375332.96255433.97486363.4229952/4/2018
ICE CREAM/SHERBET-AISLE-FROZEN DESSERTS2/11/2018 0:0021806185058089635.75241292.29786363.7466243/4/2018
ICE CREAM/SHERBET-AISLE-FROZEN DESSERTS2/18/2018 0:00210621457.455292082.98235882.68356364.949673/4/2018
ICE CREAM/SHERBET-AISLE-FROZEN DESSERTS2/25/2018 0:00210797196.855520038.58233594.53626365.430073/4/2018
ICE CREAM/SHERBET-AISLE-FROZEN DESSERTS3/4/2018 0:00218970225.858214272.92250562.45476364.5877173/4/2018
ICE CREAM/SHERBET-AISLE-FROZEN DESSERTS3/11/2018 0:00225696408.259765791.72253185.84446364.8643554/1/2018
ICE CREAM/SHERBET-AISLE-FROZEN DESSERTS3/18/2018 0:00226014643.559344004.75253957.65836365.1812094/1/2018
ICE CREAM/SHERBET-AISLE-FROZEN DESSERTS3/25/2018 0:00224440533.359573042.64249459.71726366.1499264/1/2018
ICE CREAM/SHERBET-AISLE-FROZEN DESSERTS4/1/2018 0:00234939413.263234485.76267739.33516366.184464/1/2018
Need Help
Show and Tell
Tips and Tricks
  • v-kelly-msft's avatar
    v-kelly-msft
    5 years ago

    Hi  ryan_b_fiting ,

     

    First go to query editor >create an index column;

    Then create 2 columns as below:

    rankx = RANKX(FILTER('Table',MOD('Table'[Index],4)=0),'Table'[Index],,ASC)
    4 Wk Ended = 
var _mindate=CALCULATE(MIN('Table'[Time]),FILTER('Table','Table'[rankx]=EARLIER('Table'[rankx])))
Return
_mindate+21

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

     

     

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    ryan_b_fiting , You can create a date table, and there you can create week start and week end is +28 days.

    and create a bucket based on that.

     

    example , new columns in date table

    Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
    Week End date = [Week Start date] +4
    Week = if('Date'[Week Number]<10,'Date'[Year]*10 & 'Date'[Week Number],'Date'[Year]&'Date'[Week Number])

    of you can get on the week start date and create 4-week period post that

    Min Week Start date = min([Week Start date])

    4 Week No = Quotient(datediff([Min Week Start date],date,day),28)+1

  • cyclist007's avatar
    cyclist007
    Icon for Responsive Resident rankResponsive Resident
    5 years ago
    Have you considered right clicking on the column and making a new group with bin size as 28 days
  • v-kelly-msft's avatar
    v-kelly-msft
    Icon for Community Support rankCommunity Support
    5 years ago

    Hi  ryan_b_fiting ,

     

    First go to query editor >create an index column;

    Then create 2 columns as below:

    rankx = RANKX(FILTER('Table',MOD('Table'[Index],4)=0),'Table'[Index],,ASC)
    4 Wk Ended = 
var _mindate=CALCULATE(MIN('Table'[Time]),FILTER('Table','Table'[rankx]=EARLIER('Table'[rankx])))
Return
_mindate+21

    And you will see:

    For the related .pbix file,pls see attached.

     

    Best Regards,
    Kelly

    Did I answer your question? Mark my post as a solution!

     

     

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

    Hi - 

     

    Thanks for the suggestions everyone.  However, I do not think I explained my situation properly.

     

    With my example below, what I will need to happen is when I add a new week of data (4/8/2018 in this example), all the 4 week periods shift so that the most recent always has 4 weeks bucketed into it.

     

    So if I added the 4/8/2018 data the buckets should be reflected as this:

    Time4 Wk Ended
    1/14/20181/14/2018
    1/21/20182/11/2018
    1/28/20182/11/2018
    2/4/20182/11/2018
    2/11/20182/11/2018
    2/18/20183/11/2018
    2/25/20183/11/2018
    3/4/20183/11/2018
    3/11/20183/11/2018
    3/18/20184/8/2018
    3/25/20184/8/2018
    4/1/20184/8/2018
    4/8/2018

    4/8/2018

     

    I cannot get that to happen with the solutions below.  Are there any suggestions to get this to work?

     

    Thanks in advance for all the help!