topic Wrong Totals in aggregation snapshot tables in DAX Commands and Tips

Wrong Totals in aggregation snapshot tables

truebeatles — Thu, 05 Jun 2025 13:34:34 GMT

Guys, I need your help.

I'm stacking up 2 snapshot tables, and, besides I'm using sumx with some help of copilot, I'm getting the wrong totals.
I'm trying to get the right numbers of enrollment for each period of time based on CICLO column.
Here is an example of this table, DATA_MATRICULA is primarykey to datatable:

DT_EXECUCAO_AGG\|	MAT_LIQ\|	CICLO\|	DATA_MATRICULA
20/05/2025	1	20242	10/05/2024
20/05/2025	3	20242	11/05/2024
20/05/2025	5	20242	12/05/2024
20/05/2025	0	20242	13/05/2024
20/05/2025	9	20242	14/05/2024
20/05/2025	9	20242	15/05/2024
20/05/2025	30	20252	10/05/2025
20/05/2025	10	20252	11/05/2025
20/05/2025	1	20252	12/05/2025
20/05/2025	3	20252	13/05/2025
20/05/2025	5	20252	14/05/2025
20/05/2025	1	20252	15/05/2025
04/06/2025	0	20242	10/05/2024
04/06/2025	1	20242	11/05/2024
04/06/2025	3	20242	12/05/2024
04/06/2025	5	20242	13/05/2024
04/06/2025	2	20242	14/05/2024
04/06/2025	2	20242	15/05/2024
04/06/2025	1	20252	10/05/2025
04/06/2025	0	20252	11/05/2025
04/06/2025	20	20252	12/05/2025
04/06/2025	5	20252	13/05/2025
04/06/2025	1	20252	14/05/2025
04/06/2025	1	20252	15/05/2025

Here some example of what I want to show

Rótulos de Linha	20242	20252	Total	%YoY
Campus	13	62	75	377%
Polo	27	16	43	-41%
Total Geral	40	78	118	95%

This measure is looking to get the quantity of enrollment from the same period of the last year, but I used a fixed timeslap 365 days back.

Sum MAT_LIQ_AA_YTD_Foto_D-1 =

VAR CicloPassado = CALCULATE(MAX('f_MYTABLE_252'[CICLO])) - 10

VAR MaxExecucao = CALCULATE(MAX('f_MYTABLE_252'[DT_EXECUCAO_AGG]))

RETURN

SUMX(

VALUES(d_Datatable[Date]),

VAR DataContexto = d_Datatable[Date]

VAR DataDeslocada = DATEADD(d_Datatable[Date], -IF(DataContexto < DATE(2025, 03, 01), 366, 365), DAY)

VAR MaxData = CALCULATE(MAX(d_Datatable[Date]), ALL(d_Datatable))

RETURN

IF(

DataContexto <= MaxData,

CALCULATE(

SUM('f_MYTABLE_252'[MAT_LIQ]),

'f_MYTABLE_252'[CICLO] = CicloPassado,

'f_MYTABLE_252'[DT_EXECUCAO_AGG] = MaxExecucao,

d_Datatable[Date] = DataDeslocada

BLANK()

)

Re: Wrong Totals in aggregation snapshot tables

ValtteriN — Fri, 06 Jun 2025 07:18:18 GMT

Hi,

I am not sure I understood what you want to do, but here is one way to achieve the results.

Enrollment = SUM('Table'[MAT_LIQ|])

Enrollment previous cycle =

var _curC = MAX('Table'[CICLO|])

var _cycle = MAXX(FILTER(ALL('Table'),[CICLO|]<_curC),[CICLO|])

RETURN

SUMX(FILTER(ALL('Table'),[CICLO|]=_cycle),[MAT_LIQ|])

YoY = DIVIDE([Enrollment]-[Enrollment previous cycle],[Enrollment previous cycle])

end result:

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: Wrong Totals in aggregation snapshot tables

v-venuppu — Fri, 06 Jun 2025 07:35:00 GMT

Hi truebeatles ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you ValtteriN for the prompt response.

I have tried replicating scenario using sample data.

Please find the attached pbix file for your reference.

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it!

Thank you.

Re: Wrong Totals in aggregation snapshot tables

v-venuppu — Mon, 09 Jun 2025 07:13:04 GMT

Hi truebeatles ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you ValtteriN for the prompt response.

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

Re: Wrong Totals in aggregation snapshot tables

v-venuppu — Thu, 12 Jun 2025 09:41:25 GMT

Hi truebeatles ,

I want to check if you had the opportunity to review the information provided.If the responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

Re: Wrong Totals in aggregation snapshot tables

v-venuppu — Mon, 16 Jun 2025 06:52:54 GMT

Hi truebeatles ,

I hope this information is helpful.If this answers your question, please accept it as a solution and give it a 'Kudos' so other community members with similar problems can find a solution faster.

Thank you.