topic Re: Acumulative totals in matrix cels in DAX Commands and Tips

Acumulative totals in matrix cels

sephirot2000 — Tue, 19 Dec 2023 11:24:59 GMT

Hi everyone.

I ´m trying to set a Matrix visual to show teh acumulative values of the warehouses per month.
But all I have is the sum of the movements of the current monyh. Tried ChatGPT to solve the measure but still not works as spected:

In the image you can see the sum of the movements of each month, but I spected to see in may for example, total of April + Total of May. This is my DAX formula:

suma acumulada cost amount actual =

CALCULATE(

SUM(valueEntries[costAmountActual]),

FILTER(

ALL(valueEntries[postingDate]),

valueEntries[postingDate] <= MAX(valueEntries[postingDate])

))

Any help please?

Thanks in advance.

Re: Acumulative totals in matrix cels

Fowmy — Tue, 19 Dec 2023 12:41:45 GMT

sephirot2000

Try his measure:

suma acumulada cost amount actual = CALCULATE ( SUM ( valueEntries[costAmountActual] ), FILTER ( ALL ( valueEntries ), valueEntries[postingDate] <= MAX ( valueEntries[postingDate] ) ) )

The months in the columns, are they coming from the same table as other fields or do you have a seperate date table.

Re: Acumulative totals in matrix cels

sephirot2000 — Tue, 19 Dec 2023 12:52:09 GMT

Hi and thanks for the answer.
the dates came from a separate table to filter but I can set in the same table.

Re: Acumulative totals in matrix cels

Fowmy — Tue, 19 Dec 2023 12:54:35 GMT

sephirot2000
Please try

Re: Acumulative totals in matrix cels

sephirot2000 — Tue, 19 Dec 2023 12:58:28 GMT

Not worked as spected:

Re: Acumulative totals in matrix cels

sephirot2000 — Tue, 19 Dec 2023 14:33:55 GMT

Ok my fault, I had a filter in the visual object.
The sum in now correct for the total, now I have to configure the Totals for the cells in the righ way. Thanks

Re: Acumulative totals in matrix cels

sephirot2000 — Wed, 20 Dec 2023 12:54:28 GMT

Thanks a lot, your answer put me in the righ way.

I comnplete the formula with my own filters and worked very well.

TOTALES CALCULADOS FILTRADOS 2 =

VAR TOTAL_HASTA_FIN_DE_MES =

CALCULATE(

SUM(valueEntries[costAmountActual]),

FILTER(

ALLEXCEPT(valueEntries, valueEntries[gblDim1Code]),

//ALLSELECTED(valueEntries[postingDate]),

YEAR(valueEntries[postingDate]) = YEAR(MAX(valueEntries[postingDate])) &&

MONTH(valueEntries[postingDate]) <= MONTH(MAX(valueEntries[postingDate])) &&

NOT ISBLANK(valueEntries[itemNo])

)

RETURN

TOTAL_HASTA_FIN_DE_MES