cancel
Showing results for
Search instead for
Did you mean:
Highlighted
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

Accepted Solutions
Highlighted
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
Highlighted
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.

Highlighted
Super User IV

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

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

@ me in replies or I'll lose your thread!!!

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

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Highlighted
Frequent Visitor

Hi @Greg_Deckler Thanks for quick reply.

the image again.

Highlighted
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

Highlighted
Frequent Visitor

Hi Michael, Thanks for reply,

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

Highlighted
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

Highlighted
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?

Highlighted
Microsoft

Hi,

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

Could you please share the formula reference here?

Regards

## Helpful resources

Announcements

#### Power Platform Community Conference

Check out the on demand sessions that are available now!

#### Microsoft Power Platform Communities

Check out the Winners!

#### Create an end-to-end data and analytics solution

Learn how Power BI works with the latest Azure data and analytics innovations at the digital event with Microsoft CEO Satya Nadella.

Top Solution Authors
Top Kudoed Authors