CUMULATIVE SUM

[Anonymous]

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  :

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

)

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  

[Anonymous · Accepted solution]

Hi @Anonymous ,

To calculate the cumulative total for the year try MEASURE.

Cumul AY = 
CALCULATE(
    Sum('COMPANY$Commision Entries'[Volume]),
    FILTER(
        ALL('COMPANY$Commision Entries'),
        'COMPANY$Commision Entries'[Posting Date].[Date] <= MAX('COMPANY$Commision Entries'[Posting Date].[Date])
        && YEAR('COMPANY$Commision Entries'[Posting Date].[Date]) = YEAR(MAX('COMPANY$Commision Entries'[Posting Date].[Date]))
    )
)

To calculate last year's cumulative total try MEASURE.

Cumul LY = 
CALCULATE(
    Sum('COMPANY$Commision Entries'[Volume]),
    FILTER(
        ALL('COMPANY$Commision Entries'),
        'COMPANY$Commision Entries'[Posting Date].[Date] <= MAX('COMPANY$Commision Entries'[Posting Date].[Date])
        && YEAR('COMPANY$Commision Entries'[Posting Date].[Date]) = YEAR(MAX('COMPANY$Commision Entries'[Posting Date].[Date])) - 1
    )
)

If these expressions do not give you the results you expect. Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data. We can better understand the problem and help you.

Best Regards,

Clara Gong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.