Forum Discussion

PowerRanger1's avatar
PowerRanger1
New Member
2 years ago
Solved

Cummulative Sum Calculated Column

Hi,    I'm looking to sum cummulative sales month on month per fiscal year(FY) my data is as per the below & the result i'm looking for is a calculated column called "FY Cummulative" as shown.   ...
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi PowerRanger1 ,

    Thanks tamerj1  for your replies, allow me to provide another insight:
    1. create new index column in power query editor.


    2. create calculated column and write expression.

    Column =
CALCULATE(
SUM('Table'[Revenue]),
FILTER(
ALL('Table'),
'Table'[FY] = EARLIER('Table'[FY]) &&
'Table'[Index] <= EARLIER('Table'[Index])
)
)

    If your Current Period does not refer to this, please clarify in a follow-up reply.

     

    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.

tamerj1's avatar
tamerj1
Community Champion
2 years ago

Hi PowerRanger1 

Please try

FY Cummulative =
IF (
HASONEVALUE ( 'Date'[FY] ),
SUMX (
FILTER (
CALCULATETABLE (
SUMMARIZE ( 'Date', 'Date'[FY], 'Date'[Month Name] ),
ALLSELECTED ()
),
'Date'[FY] = MAX ( 'Date'[FY] )
),
[Revenue]
)
)