Forum Discussion
Starting Windows function with 0?
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?
[sales] -
CALCULATE(
[sales],
OFFSET(
-1,
SUMMARIZE(ALLSELECTED('Datatable'), Datatable[Division Name], Datatable[Eff Date]),
ORDERBY(Datatable[Eff Date]),
KEEP,
PARTITIONBY(Datatable[Division Name])
)
)
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])
2 Replies
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])