Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

groups and binning

Hello,    I have list of month year in a table. (Jan 2018, Feb 2018, Mar 2018, Apr 2018, May 2018, June 2018, Jul 2018, Aug 2018, Sept 2018, Oct 2018, etc), I want to group it every 2 months, so, F...
Need Help
Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hi Anonymous ,

 

Please share a part of sample data so that we can test and coding formula on it.

 

Notice: if you not have permission to upload sample file, please upload to onedrive or google drive and share link here.

 

Regards,

Xiaoxin Sheng

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Hello Anonymous ,

 

I did mention the ultimate goal is to be able to group Dec 2017 and Jan 2018 as Jan 2018, Jan and Feb 2018 as Feb 2018 and so on. 

 

GroupingPeriod
Jan-18Dec-17
Jan-18Jan-18
Feb-18Jan-18
Feb-18Feb-18
Mar-18Feb-18
Mar-18Feb-18

 

Thank you & rgds,

Lina

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Here is a possible solution, I will just assume that you are calculating 2 months of sale:

     

    You can start out with adding a date column:

    Date = 
    DATE(
        VALUE(
            RIGHT(Table2[Yr-Month];2)
        )+2000;
        SWITCH(
            LEFT(Table2[Yr-Month];3);
            "Jan";1;"Feb";2;"Mar";3;"Apr";4;"May";5;"Jun";6;
            "Jul";7;"Aug";8;"Sep";9;"Oct";10;"Nov";11;"Dec";12
        );
        1
    )

    And then you can group two months of data with a measure:

    Sales2M = 
VAR currentMonthDate = IF(HASONEVALUE(Table2[Date]);VALUES(Table2[Date]))
RETURN
IF(
    NOT(ISBLANK(currentMonthDate));
    [Total Sales] + CALCULATE([Total Sales];ALL(Table2[Yr-Month]);Table2[Date] = EDATE(currentMonthDate;-1))
)


    Note that this function will only work when you filter on Period (in my case Yr-Month).

     

    Regards,

    Kristjan76

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    Hi Anonymous ,

     

    Which condition you used to split one month to two part?

     

    Regards,
    Xiaoxin Sheng

  • Anonymous's avatar
    Anonymous
    Not applicable
    7 years ago

    HI Anonymous, 

     

    Sorry, I'm not sure if I understand your question?