Forum Discussion

AndreaLorenzo's avatar
AndreaLorenzo
Frequent Visitor
1 year ago
Solved

Cummulative sum for different years with month filter

Hi everybody,

 

I am struggling to get a cummulative sum when displaying data in a matrix for different years and with a month filter.

 

I can get this year's amount correct, but for the previous years, it is displaying the whole sales amount of the year for each product, instead of the cummulative sales amount for the selected month of that year.  

 

I have tried several formulas, and the one that works more closely to what I am trying to achieve is this one, but it has the problem mentioned before:

 

 

 

 

 

Ventas Acumuladas =
CALCULATE(
SUM(Ventas_Ppto_Forecasting[Importe_Real]),
FILTER(
ALL(PBI_Calendario),  
PBI_Calendario[Fecha] <= MAX(PBI_Calendario[Fecha])  
)
)

 

 

 

How do I modify the formula so I get the cummulative sum for the selected month in each year displayed in the matrix?


Thanks a lot for your help!

 

 

  • AndreaLorenzo Try using this

    DAX
    Ventas Acumuladas =
    CALCULATE(
    SUM(Ventas_Ppto_Forecasting[Importe_Real]),
    FILTER(
    ALL(PBI_Calendario),
    PBI_Calendario[Año] = MAX(PBI_Calendario[Año]) &&
    PBI_Calendario[Mes] <= MAX(PBI_Calendario[Mes])
    )
    )

     

2 Replies

  • AndreaLorenzo Try using this

    DAX
    Ventas Acumuladas =
    CALCULATE(
    SUM(Ventas_Ppto_Forecasting[Importe_Real]),
    FILTER(
    ALL(PBI_Calendario),
    PBI_Calendario[Año] = MAX(PBI_Calendario[Año]) &&
    PBI_Calendario[Mes] <= MAX(PBI_Calendario[Mes])
    )
    )

     

    • AndreaLorenzo's avatar
      AndreaLorenzo
      Frequent Visitor

      bhanu_gautam Sorry, you were right! The problem was I was using a wrong "year" field when displaying the matrix... My bad!😅