cancel
Showing results for
Did you mean: Frequent Visitor

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

the table shouldlook like these Thanks for help.

1 ACCEPTED SOLUTION  Microsoft

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

8 REPLIES 8  Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Mastering Power BI 2nd Edition  DAX is easy, CALCULATE makes DAX hard... Frequent Visitor

Hi @Greg_Deckler Thanks for quick reply.

the image again.   Microsoft

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 Frequent Visitor

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

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 Frequent Visitor

Thanks Michael it works.

But I still have 1 question,

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

Hi,

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

Could you please share the formula reference here?

Regards Frequent Visitor

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.  