Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
diogonesbitt
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

cohort help

 

Thanks for help.

 

1 ACCEPTED SOLUTION

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

View solution in original post

8 REPLIES 8
Greg_Deckler
Super User
Super User

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



Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Hi @Greg_Deckler Thanks for quick reply.

 

the image again.

cohort data

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

Hi Michael, Thanks for reply,

 

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

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

Thanks Michael it works.

 

But I still have 1 question,

 

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

 

 

Hi,

 

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

 

Could you please share the formula reference here?

Regards

diogonesbitt
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

cohort help

 

Thanks for help.

 

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

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

Jan NL Carousel

Fabric Community Update - January 2025

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