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 s...
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!

     

     

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!