cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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 Employee

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!:
The Definitive Guide to Power Query (M)

DAX is easy, CALCULATE makes DAX hard...
Frequent Visitor

Hi @Greg_Deckler Thanks for quick reply.

the image again.

Microsoft Employee

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 Employee

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 Employee

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

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors