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 IV

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

---------------------------------------

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

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.

Announcements

#### Happy New Year from Power BI

This is a must watch for a message from Power BI!