help with partial total to cohort

[diogonesbitt]

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 with the total of the month

the table shouldlook like these

Thanks for help.

[Greg_Deckler]

I don't see your image. Sample data would be great too.

[diogonesbitt]

Hi @Greg_Deckler Thanks for quick reply.

the image again.

[v-micsh-msft]

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]

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 · Accepted solution]

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]

Thanks Michael it works.

But I still have 1 question,

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

[v-micsh-msft]

Hi,

What " 'Table'[Date].MonthNum " indicates here, and how it works?

Could you please share the formula reference here?

Regards

[diogonesbitt]

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 with the total of the month

the table shouldlook like these

Thanks for help.