none
Grouping based on Month Range

    Question

  • Hi, I have Date column called active-Date.

    I want to show my tables as

                                       March12-June12          Sept12-Nov12

    sum of assets                     120                           400

    sum of activities                  150                           300

    sum of games                      200                           600

    So I have to use that active-Date as COLUMNS in PowerPivot Field List which will be month specific duration i.e. between March to Jun like this.

    How to do that? Do I need to write DAX for that?

    Thursday, January 09, 2014 10:55 AM

Answers

  • But if suppose my requirement is like I want range as March12 - June13 then??

    As I wrote, you can define it for each month as you like, so you can e.g. use such a statement:

    =SWITCH(MONTH(Table1[Date])
           , 1, "Jan-Feb " & YEAR(Table1[Date])
           , 2, "Jan-Feb " & YEAR(Table1[Date])
           , 3, "Mar-Jun " & YEAR(Table1[Date])
           , 4, "Mar-Jun " & YEAR(Table1[Date])
           , 5, "Mar-Jun " & YEAR(Table1[Date])
           , 6, "Mar-Jun " & YEAR(Table1[Date])
           , ...


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, January 09, 2014 12:54 PM

All replies

  • Please provide your table structure...
    Thursday, January 09, 2014 11:01 AM
  • Hi, consider any sort of table having one column as Date column & remaining anything.
    I just wanted to sum up the values based on month range.
    Thursday, January 09, 2014 11:05 AM
  • Hello,

    Yes, you could add a calculated column to get a date range value. You could e.g. use the SWITCH function to define a range value for every month, like

    =SWITCH(MONTH(Table1[Date]), 1, "A-" & YEAR(Table1[Date]), 2, "A-" & YEAR(Table1[Date]), 3, "B-" & YEAR(Table1[Date]), 4, "B-" & YEAR(Table1[Date]), 5, "C-" & YEAR(Table1[Date]), ....., 12, "F-" & YEAR(Table1[Date]))


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, January 09, 2014 11:13 AM
  • Get month number inside a field and use that to SUM the Asset,Activity etc by adding FILTER on required months (ie 3 to 6 for Mar-June etc) as below

    =CALCULATE(SUM(Field),FILTER(Sheet,MonthField >= 3 && MonthField <=6))


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Thursday, January 09, 2014 12:33 PM
  • Hi Olaf,

    Yes perfect! that will do.

    But if suppose my requirement is like I want range as

    March12 - June13 then???

    Thursday, January 09, 2014 12:37 PM
  • Hi Visakh, agree with ur answer.

    But I don't want to add more Calculated fields of existing columns for just specifying month range.

    Instead of that I can add extra column for month related calculations.
    Thursday, January 09, 2014 12:47 PM
  • But if suppose my requirement is like I want range as March12 - June13 then??

    As I wrote, you can define it for each month as you like, so you can e.g. use such a statement:

    =SWITCH(MONTH(Table1[Date])
           , 1, "Jan-Feb " & YEAR(Table1[Date])
           , 2, "Jan-Feb " & YEAR(Table1[Date])
           , 3, "Mar-Jun " & YEAR(Table1[Date])
           , 4, "Mar-Jun " & YEAR(Table1[Date])
           , 5, "Mar-Jun " & YEAR(Table1[Date])
           , 6, "Mar-Jun " & YEAR(Table1[Date])
           , ...


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    Thursday, January 09, 2014 12:54 PM
  • Hi Olaf,

    Thanks a lot! The problem is solved.

    But Is it efficient way? or there is no other option than this one.

    Thursday, January 09, 2014 1:26 PM
  • But Is it efficient way?

    You can use cascaded IF statements instead:

    =IF(MONTH(Table1[Date]) >= 3 && MONTH(Table1[Date]) < 7
       , "Mar-Jun " & YEAR(Table1[Date])
       , IF(MONTH(Table1[Date]) >= 7 && MONTH(Table1[Date]) < 12
           , "Jul-Oct " & YEAR(Table1[Date])
           , "Nov-Feb " & YEAR(Table1[Date]) 
           )
       )


    Olaf Helper

    [ Blog] [ Xing] [ MVP]



    Thursday, January 09, 2014 3:51 PM
  • Okay Thnks!

    1 more query.

    Now I am ready with my calculated column of Month range to drag that in to COLUMNS area in PowerPivot.

    If I want to add that calculated column in COLUMNS area TWICE(to show two different Month Range), then how to add it twice as it is not allowing me to add.



    Friday, January 10, 2014 9:19 AM