SUM by Product and Date

[BEcomeProPBI]

Hi I have a table with 3 columns
1. Client number

2. Sales

3. Date

The sales are monthly so client number repeats for each date. I need to sum the first sale from each client number, there are two problems, one each client number has a different first month and first year, and since the client number keep repeating, it is hard to filter by date only. The number of rows are very high.

Can you help me?

[V-lianl-msft · Accepted solution]

Hi @BEcomeProPBI ,

Based on your description, what you want to calculate is a summary of the first sales of each client number.

You could try the DAX like below:

Measure = 
var min_date = CALCULATE(MIN(Sheet4[Date]),ALLEXCEPT(Sheet4,Sheet4[Client number]))
return CALCULATE(SUM(Sheet4[Sales]),FILTER(Sheet4,Sheet4[Date]=min_date))

Sample .pbix 

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

@BEcomeProPBI ,

Try a new measure like

Measure =
VAR __id = MIN ( 'Table'[Client number] )
VAR __date = CALCULATE ( MIN( 'Table'[Date] ), ALLSELECTED ( 'Table' ), 'Table'[Client number] = __id )
RETURN CALCULATE ( sum ( 'Table'[Sales] ), VALUES ( 'Table'[Client number] ), 'Table'[Client number] = __id, 'Table'[date] = __date )