Forum Discussion
Solved
Summarize - how keep null value
I have data: Data of sales, Product number and Sales Quantity. There are days when there is no sale. For example:
| Date | No | Quantity |
| 01.04.2023 | 123 | 6 |
| 03.04.2023 | 123 | 9 |
| 05.04.2023 | 123 | 3 |
| 01.04.2023 | 987 | 12 |
| 02.04.2023 | 987 | 13 |
| 04.04.2023 | 987 | 45 |
I try to calculate cumulative sum of quantity for last 3 days. It means, for 6 April it will be period 4 April - 6 April. I try with measure:
Sum of last 3 days =
CALCULATE (
SUMX( Table1, Table1[Quantity] ),
FILTER (
ALLEXCEPT ( Table1, Table1[No] ),
Table1[Date] <= SELECTEDVALUE( Table1[Date] ) &&
Table1[Date] >= SELECTEDVALUE( Table1[Date] ) - 3
)
)
It works and i have data for every date even though there are no sales:
| Date | No | Quantity | Sum of last 3 days |
| 01.04.2023 | 123 | 6 | 6 |
| 02.04.2023 | 123 | 6 | |
| 03.04.2023 | 123 | 9 | 15 |
| 04.04.2023 | 123 | 9 | |
| 05.04.2023 | 123 | 3 | 12 |
| 06.04.2023 | 123 | 2 | 5 |
| 01.04.2023 | 987 | 12 | 12 |
| 02.04.2023 | 987 | 13 | 25 |
| 03.04.2023 | 987 | 25 | |
| 04.04.2023 | 987 | 45 | 58 |
| 05.04.2023 | 987 | 45 | |
| 06.04.2023 | 987 | 45 |
Now i'd like use summarize function and save this data to new table.
Table2 = SUMMARIZE(
Table1,
Table1[Date],
Table1[No],
"Sales 3 days", CALCULATE(Sum of last 3 days)
)
and then i lose records with data with no daily sales...
| Date | No | Quantity | Sum of last 3 days |
| 01.04.2023 | 123 | 6 | 6 |
| 03.04.2023 | 123 | 9 | 15 |
| 05.04.2023 | 123 | 3 | 12 |
| 01.04.2023 | 987 | 12 | 12 |
| 02.04.2023 | 987 | 13 | 25 |
| 04.04.2023 | 987 | 45 | 58 |
I'd like to keep all records same as adding a measure to Table1.
Any ideas?
