filling up a date table with an aggregated value per catergory

[bklyn3]

Hello

I have a table with transaction date (not consecutive and not unique), client name and sales

Date	Client	Future Sales
01/01/2022	A	$50,000
01/01/2022	A	$18,008
01/04/2022	B	$30,000
01/07/2022	A	$49,000
01/10/2022	B	$78,000

I would like to create a new table that shows me how much each client plan on spending in the next 8 weeks. 

> The new table should have one date for each days in hte next 8 weeks. 

> If a client doesnt' plan on spending on specific day, the reported number should be the last reported amount (initial value is $0) 

> if a client spend more than once on a given day, it should be the sum for the day.  

How do I achieve that in DAX? 

thank you

[v-yiruan-msft · Accepted solution]

Hi @bklyn3 ,

According to your description, You want to generate a new table that displays the date of each customer from today to the eighth week, and sums the time corresponding to the [Future Sales] field (0 if there is no data), and [Future Sales] Fields may be empty or duplicate..Right?

(1)This is my test data:

(2) We can click “New Table” and enter :

Table2 =
ADDCOLUMNS (
    CROSSJOIN ( VALUES ( 'Test'[Client] ), CALENDAR ( TODAY (), TODAY () + 56 ) ),
    "Feture Salse",
        CALCULATE (
            SUM ( 'Test'[Future Sales] ),
            TREATAS ( { ( [Client], [Date] ) }, Test[Client], Test[Date] )
        )
)

(3) Then we can meet your need, the result is as follows:

If this method does not meet your needs, can you provide us with detailed input and output sample data in tabular form so that we can better solve the problem for you.

Best Regards