Starting Windows function with 0?

[Anonymous]

Hi, 

I have a table visualization which takes sales by division and calculates the changes between each month by division. I'll paste the code I used below, but the problem is that the first data point for each division (for example, January 2022) returns a "change" equal the amount for that month. For instance:

Division	Date	Sales	Change
1	1/2022	87	87
1	2/2022	80	-7
1	3/2022	125	45
2	1/2022	42	42

Is there any way I can modify the formula below to have the first incidence of a division (in the example, the first and fourth column) to show 0 instead of reflecting the amount of sales?

Change =
[sales] -
CALCULATE(
[sales],
OFFSET(
-1,
SUMMARIZE(ALLSELECTED('Datatable'), Datatable[Division Name], Datatable[Eff Date]),
ORDERBY(Datatable[Eff Date]),
KEEP,
PARTITIONBY(Datatable[Division Name])
)
)

Thank you for any help or guidance!

[amitchandak · Accepted solution]

@Anonymous , Try like

Change =
[sales] -
coalesce(
CALCULATE(
[sales],
OFFSET(
-1,
SUMMARIZE(ALLSELECTED('Datatable'), Datatable[Division Name], Datatable[Eff Date]),
ORDERBY(Datatable[Eff Date]),
KEEP,
PARTITIONBY(Datatable[Division Name])
)
),[sales])

[Anonymous]

Thank you so much! So the solution was to wrap the whole calculate statement in a coalesce with [sales]. That makes a lot of sense, and I appreciate your help!