March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
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.
Solved! Go to 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
I don't see your image. Sample data would be great too.
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
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.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
123 | |
85 | |
85 | |
70 | |
51 |
User | Count |
---|---|
205 | |
153 | |
97 | |
79 | |
69 |