Forum Discussion
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
- bhanu_gautam
Super User
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])
)
)- AndreaLorenzoFrequent Visitor
bhanu_gautam Sorry, you were right! The problem was I was using a wrong "year" field when displaying the matrix... My bad!😅