Forum Discussion

diogonesbitt's avatar
diogonesbitt
Frequent Visitor
9 years ago
Solved

help with partial total to cohort

Hi everyone, after one day trying all suggestions on this forum I will ask for help because I cant find the solution for something that seems simple.   To create a cohort matrix I need a column wit...
  • v-micsh-msft's avatar
    v-micsh-msft
    9 years ago

    Hi diogonesbitt,

    I have already added the year column as a filter in the formula, so it will only calculate the value within the same year range, and if you woud like to sum the value in month range, create a column which format with year and month with the date column, then use that column as a filter.

    GroupTotal = sumx(

                       filter(

                          'Table',

                           And('Table'[Mes]=earlier('Table'[mes]),

                                     ‘Table’[Ano]=Earlier(‘Table’[Ano]))

                               ),

                       'Table'[Count of id])

    Regards

v-micsh-msft's avatar
v-micsh-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

Hi diogonesbitt,

 

We could create a calculated column to calculate the group total, with the formula below:

GroupTotal = sumx(

                   filter(

                      'Table',

                       And('Table'[Mes]=earlier('Table'[mes]),

                                 ‘Table’[Ano]=Earlier(‘Table’[Ano]))),

                   'Table'[Count of id])

Check to see if this would meet your requirements.

Regards

  • diogonesbitt's avatar
    diogonesbitt
    Frequent Visitor
    9 years ago

    Hi Michael, Thanks for reply,

     

    The problem is that in thant case May 2015 will sum with may 2014. I'm I wrong?

    • v-micsh-msft's avatar
      v-micsh-msft
      Icon for Microsoft Employee rankMicrosoft Employee
      9 years ago

      Hi diogonesbitt,

      I have already added the year column as a filter in the formula, so it will only calculate the value within the same year range, and if you woud like to sum the value in month range, create a column which format with year and month with the date column, then use that column as a filter.

      GroupTotal = sumx(

                         filter(

                            'Table',

                             And('Table'[Mes]=earlier('Table'[mes]),

                                       ‘Table’[Ano]=Earlier(‘Table’[Ano]))

                                 ),

                         'Table'[Count of id])

      Regards

      • diogonesbitt's avatar
        diogonesbitt
        Frequent Visitor
        9 years ago

        Thanks Michael it works.

         

        But I still have 1 question,

         

        I cant user the 'Table'[Date].MonthNum instead new column called 'Table'.[Month]. Why?