Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Cumulative sums per month

Hi everyone !

 

I already made some research but what I found did not help me.

I want to have the cumulative sum for each month in a third column for actual year and in 4th column for last year.

The two first columns are built like this :

VolLastYear = CALCULATE(
Sum('COMPANY$Commision Entries'[Volume]),
FILTER('COMPANY$Commision Entries','COMPANY$Commision Entries'[Année]=[YearActual]-1)
)
 
VolActualYear = CALCULATE(
Sum('COMPANY$Commision Entries'[Volume]),
FILTER('COMPANY$Commision Entries','COMPANY$Commision Entries'[Année]=[YearActual])
)
My posting date is in the table COMPANY$Commision Entries in a date format.
 
I tried to use a quick measure proposed by Power BI, it works well for actual year but does not for last year (it stays blank), so I'm looking for something new.
I tried the multiple solutions proposed online but it seems that it is not for me.
 
 
Thank you for your help 🙂 
Need Help

2 Replies

  • ExcelMonke's avatar
    ExcelMonke
    Icon for Impactful Individual rankImpactful Individual
    2 years ago

    Hello,

    You could consider a measure similar to your first one. Depending on how your month column is set up, you can consider doing it a couple different ways. 
    If you have month as numbers (e.g. 1 - 12) you can consider:

    VolLastYear = 
VAR _LastMonthJan = 
IF('COMPANY$Commision Entries'[MonthNumber] = 1, 12,'COMPANY$Commision Entries'[MonthNumber]-1)

VAR _LastMonthJanCalc = 
CALCULATE(
Sum('COMPANY$Commision Entries'[Volume]),
FILTER('COMPANY$Commision Entries','COMPANY$Commision Entries'[MonthNumber]=_LastMonth && 'COMPANY$Commision Entries'[Annee]-1)
)

VAR _LastMonthCalc = CALCULATE(
Sum('COMPANY$Commision Entries'[Volume]),
FILTER('COMPANY$Commision Entries','COMPANY$Commision Entries'[MonthNumber]=_LastMonth))

RETURN
IF('COMPANY$Commision Entries'[MonthNumber] = 1, _LastMonthJanCalc,_LastMonthCalc)
    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      Hi, I'm sorry, it's not working, I want to result to be just as cumul AY for the previous year

       

      The measure proposed by Power Bi for the actual year is this one, but when I try to adapt it, it is blank 

      Cumul AY =
      IF(
          ISFILTERED('CINOCO$Commision Entries'[Posting Date]),
          ERROR("Les mesures rapides de Time Intelligence peuvent être regroupées ou filtrées seulement par la hiérarchie de dates ou les colonnes de dates principales fournies par Power BI."),
          TOTALYTD([VolActualYear], 'CINOCO$Commision Entries'[Posting Date].[Date])
      )