Cumulative sum (YTD) based on other columns

[zervino]

Hi,

I have data in this format:

Year	Month	Customer	Sales
2025	1	ABC	100
2025	2	ABC	80
2025 FC	1	ABC	50
2025 FC	2	ABC	30
2025 FC	3	ABC	10

The output I'm looking for is:

Year	Month	Customer	Sales	Cum_sales
2025	1	ABC	100	100
2025	2	ABC	80	180
2025 FC	1	ABC	50	50
2025 FC	2	ABC	30	80
2025 FC	3	ABC	10	90

I believe I cannot use the TOTALYTD formula because my year is not a number, but it's more like a "Plan name".

How can I create a YTD by year (plan), month and customer?

Thanks.

[Elena_Kalina · Accepted solution]

Hi @zervino 

First, create a calculated column to identify each plan's sequence

PlanSortKey = 
VAR PlanName = LEFT('Table'[Year], 4)
VAR IsFC = IF(CONTAINSSTRING('Table'[Year], "FC"), 1, 0)
RETURN
PlanName & IsFC & FORMAT('Table'[Month], "00")

Then create your cumulative sales measure

Cumulative Sales = 
VAR CurrentRowPlan = SELECTEDVALUE('Table'[Year])
VAR CurrentRowMonth = SELECTEDVALUE('Table'[Month])
VAR CurrentRowCustomer = SELECTEDVALUE('Table'[Customer])

RETURN
CALCULATE(
    SUM('Table'[Sales]),
    FILTER(
        ALL('Table'),
        'Table'[Year] = CurrentRowPlan &&
        'Table'[Month] <= CurrentRowMonth &&
        'Table'[Customer] = CurrentRowCustomer
    )
)

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[v-mdharahman]

Hi @zervino,

Thanks for reaching out to the Microsoft fabric community forum.

It looks like you are looking for a way to calculate the cumulative sum of Sales data based on month column but as your month column doesn't contain only numerical data but mixed one, you are facing issues in calculating the cummulative sum. As @ThxAlot, @Demert, @Elena_Kalina and @Nasif_Azam have already responded to your query, kindly go through their responses and check if your issue can be resolved.

I would also take a moment to thank @ThxAlot, @Demert, @Elena_Kalina and @Nasif_Azam, for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

If I misunderstand your needs or you still have problems on it, please feel free to let us know.  

Best Regards,
Hammad.
Community Support Team

[v-mdharahman]

Hi @zervino,

As we haven’t heard back from you, so just following up to our previous message. I'd like to confirm if you've successfully resolved this issue or if you need further help.

If yes, you are welcome to share your workaround and mark it as a solution so that other users can benefit as well. If you find a reply particularly helpful to you, you can also mark it as a solution.
If so, it would be really helpful for the community if you could mark the answer that helped you the most. If you're still looking for guidance, feel free to give us an update, we’re here for you.

Best Regards,

Hammad.

[v-mdharahman]

Hi @zervino,
I just wanted to follow up on your thread. If the issue is resolved, it would be great if you could mark the solution so other community members facing similar issues can benefit too.
If not, don’t hesitate to reach out, we’re happy to keep working with you on this. 

Best Regards,

Hammad.

[v-mdharahman]

Hi @zervino,
We noticed there hasn’t been any recent activity on this thread. If your issue is resolved, marking the correct reply as a solution would be a big help to other community members. If you still need support, just reply here and we’ll pick it up from where we left off.

Best Regards,

Hammad.

[ThxAlot · Accepted solution]

[Demert]

Hello @zervino ,

I would suggest following calculation: 

TotalYTD = 
CALCULATE(SUM(Table[Sales])
,ALLSELECTED(Table) ,
Table[Year]= SELECTEDVALUE(Table[Year])
&& Table[Month]<= SELECTEDVALUE(Table[Month]) 
&& Table[Customer]= SELECTEDVALUE(Table[Customer]))

I also another customer to the data so you can see it's per customer.

Take all selected records from the table, because the table visual will automatically filter by customer, month, and year — and we don't want that.
Next, we only care about records that have the same value in the year column.
We sum the values from the previous and current month.
We don’t care about months in the future.
Also, we should filter by customer, since the table is per customer.

[Elena_Kalina · Accepted solution]

Hi @zervino 

First, create a calculated column to identify each plan's sequence

PlanSortKey = 
VAR PlanName = LEFT('Table'[Year], 4)
VAR IsFC = IF(CONTAINSSTRING('Table'[Year], "FC"), 1, 0)
RETURN
PlanName & IsFC & FORMAT('Table'[Month], "00")

Then create your cumulative sales measure

Cumulative Sales = 
VAR CurrentRowPlan = SELECTEDVALUE('Table'[Year])
VAR CurrentRowMonth = SELECTEDVALUE('Table'[Month])
VAR CurrentRowCustomer = SELECTEDVALUE('Table'[Customer])

RETURN
CALCULATE(
    SUM('Table'[Sales]),
    FILTER(
        ALL('Table'),
        'Table'[Year] = CurrentRowPlan &&
        'Table'[Month] <= CurrentRowMonth &&
        'Table'[Customer] = CurrentRowCustomer
    )
)

If this post helps, then please consider Accepting as solution to help the other members find it more quickly, don't forget to give a "Kudos" – I’d truly appreciate it! 

Thank you.

[Nasif_Azam]

Hey @zervino ,

For this scenario, you are right that you cannot use the standard TOTALYTD function since the "Year" is a string (e.g., "2025 FC") rather than a numeric value. Possible solution:

1. Ensure your data is sorted by "Year", "Month", and "Customer" properly.

2. Create a new calculated column for "cumulative_sales":

   cumulative_sales = 
   CALCULATE(
       SUM('YourTable'[Sales]),
       FILTER(
           'YourTable',
           'YourTable'[Customer] = EARLIER('YourTable'[Customer]) && 
           'YourTable'[Year] = EARLIER('YourTable'[Year]) && 
           'YourTable'[Month] <= EARLIER('YourTable'[Month])
       )
   )

If you found this solution helpful, please consider accepting it and giving it a kudos (Like) it’s greatly appreciated and helps others find the solution more easily.

Best Regards,
Nasif Azam