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!:
Power BI Cookbook Third Edition (Color)

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

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

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

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors