topic Re: calculate running sales for previous years in DAX Commands and Tips

calculate running sales for previous years

Julia1234 — Thu, 03 Aug 2023 19:11:53 GMT

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

Re: calculate running sales for previous years

Jihwan_Kim — Fri, 04 Aug 2023 03:39:52 GMT

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

Re: calculate running sales for previous years

Julia1234 — Wed, 09 Aug 2023 18:55:38 GMT

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