topic Re: Cumulative sums per month in DAX Commands and Tips

Cumulative sums per month

Anonymous — Wed, 12 Jun 2024 14:48:28 GMT

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 🙂

Re: Cumulative sums per month

ExcelMonke — Wed, 12 Jun 2024 14:56:32 GMT

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)

Re: Cumulative sums per month

Anonymous — Thu, 13 Jun 2024 08:28:38 GMT

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])

)