groups and binning

[Anonymous]

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, Feb 2018 - actually consist of Jan and Feb 2018, Mar 2018 is actually consist Feb 2018 and Mar 2018. How can I achieve this? I tried grouping, but the group only allows to add in the list once. Please advice if you have other ways to do this! Thank you!

Thanks & Rgds,

Lina

[Anonymous]

For the groupping you can do something like

YourCalculatedColumn = 
Roundup(Month(<your date>)/2,0)

If you do not have a date, then you could just use SWITCH

YourCalculatedColumn =
SWITCH(
  TRUE(),
  LEFT(< month column >, 2) = "Jan", "Feb 2018",
  LEFT(< month column >, 2) = "Feb", "Feb 2018",
  ...
)
 

[Anonymous]

Hi, 

I don't have a date, it's just showing month and year. By using the second solution, it means I need to add in all possible combinations. What if I have all months from 2015 - up to anytime in the future? Is there any way to do this dynamically?

Thanks & Rgds, 

Lina

[Anonymous]

Then you could do:

YourCalculatedColumn =
SWITCH(
  TRUE(),
  LEFT(< month column >, 2) = "Jan", "Feb " & RIGHT(< month column >, 4) ,
  LEFT(< month column >, 2) = "Feb", "Feb " & RIGHT(< month column >, 4),
  ...
)

If this solves the problem, please like it and mark it as solution.

Best regards,

Kristjan76

[Anonymous]

HI!, 

I tried to use your first solution, because turns out the column is in date form, but it's not what I want to do. 

so what happen is Jan and Feb = Feb

March and April = March

With this, I only have 6 data groups in a year, it's supposed to be 12. 

but what I wanted is

Dec and Jan = Jan

Jan and Feb = Feb, 

Feb and March = March

March and April = April.

[Anonymous]

I created a table consists of 2 columns Grouping and Period. 

Content of the data will be

1. Grouping - Period
2. Jan 2018 - Dec 2017
3. Jan 2018 - Jan 2018
4. Feb 2018 - Jan 2018
5. Feb 2018 - Feb 2018
6. Mar 2018 - Feb 2018
7. Mar 2018 - Mar 2018
8. and so on...

Then match the period column with the same in other table, and group the chart based on Grouping column.

It's a bit hardcoded, but it works for now...if anybody have a simpler way/more dynamic way to do this, I would appreciate. 

Thanks & Rgds,

Lina

[Anonymous]

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]

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. 

Grouping	Period
Jan-18	Dec-17
Jan-18	Jan-18
Feb-18	Jan-18
Feb-18	Feb-18
Mar-18	Feb-18
Mar-18	Feb-18

Thank you & rgds,

Lina

[Anonymous]

Hi @Anonymous ,

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

Regards,
Xiaoxin Sheng

[Anonymous]

HI @Anonymous, 

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

[Anonymous]

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