Forum Discussion

sephirot2000's avatar
sephirot2000
Frequent Visitor
2 years ago
Solved

Acumulative totals in matrix cels

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.
  • Fowmy's avatar
    Fowmy
    2 years ago

    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.

6 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    2 years ago

    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.

    • sephirot2000's avatar
      sephirot2000
      Frequent Visitor
      2 years ago

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

    • sephirot2000's avatar
      sephirot2000
      Frequent Visitor
      2 years ago

      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