Skip to main content
cancel
Showing results for
Search instead for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## calculate running sales for previous years

Hi , I am looking for help to calculate calculate running/rolling sum sales for Previous years Contract Amt.
Year | Month | CustomerGroup| Customer | Contract | Contract Amt | Previous years Contract Amt

2020  11            1                     Smith         S12345      \$100                  0

2021  12            1                     Smith         S12345      \$200                  \$100

2022    1            1                     Smith         S12345      \$90                    \$300

2022    1            1                     Smith         S12345      \$120                  \$300

2023 ...

I tried this, but this returns Contract Amt for ALL Customers

Previous years Contract Amt = CALCULATE (
SUM ( Table[Contract Amt] ),
ALL(Table) ,Table[Year] < EARLIER( Table[Year]
))

Thank you in advance

1 ACCEPTED SOLUTION
Super User

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

It is for creating a new column.
I hope the below can provide some ideas on how to create a solution for your datamodel.

``````Prev Year Contract Amount CC =
SUMX (
WINDOW (
1,
ABS,
-1,
REL,
GROUPBY (
Data,
Data[Year],
Data[Customer],
"@amountsum", SUMX ( CURRENTGROUP (), Data[Contract Amount] )
),
ORDERBY ( Data[Year] ),
,
PARTITIONBY ( Data[Customer] )
),
[@amountsum]
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Click here and Go to My LinkedIn Page

2 REPLIES 2
Super User

Hi,

I am not sure if I understood your question correctly, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

It is for creating a new column.
I hope the below can provide some ideas on how to create a solution for your datamodel.

``````Prev Year Contract Amount CC =
SUMX (
WINDOW (
1,
ABS,
-1,
REL,
GROUPBY (
Data,
Data[Year],
Data[Customer],
"@amountsum", SUMX ( CURRENTGROUP (), Data[Contract Amount] )
),
ORDERBY ( Data[Year] ),
,
PARTITIONBY ( Data[Customer] )
),
[@amountsum]
)
``````

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.

Click here and Go to My LinkedIn Page

Frequent Visitor

Thank you @JihwanKim , this is very helpful, I was looking for exactly this - dax window function.

## Helpful resources

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Join our Community Sticker Challenge

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

#### Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

#### Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors