Summarize - how keep null value

[zolta_k]

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?

[tamerj1 · Accepted solution]

Hi @zolta_k 

you need to have a dimDate table connected to the main table then the table dax would be

Table2 =
ADDCOLUMNS (
CROSSJOIN ( VALUES ( 'Date'[Date] ), VALUES ( Table1[No] ) ),
"Sales 3 days", [Sum of last 3 days]
)