Calculate formula and apply a Date filter + select only the first transaction date by client

[Vincem35]

Dear Community, 

I am working with a transaction table such as below:

Client ID	Transaction Date	Sales
1	1/1/2019	$  100.00
1	1/1/2018	$  200.00
1	3/1/2018	$  355.00
1	4/5/2020	$      5.00
2	1/1/2019	$  100.00
2	1/1/2018	$  200.00
2	3/1/2018	$  355.00
2	4/5/2020	$      5.00

I am seeking your help as i would like to calculate the Sum of Total Sales for the first Transaction Date of each client for a specific period (e.g 2018)
The result using the above table should be: 400$

I have the folllowing formula:

Test =CALCULTATE(
SUM([Sales]), 
[Transaction Date] >="2018-01-01" && [Transaction Date] <="2018-12-31")

But would need to add the filter for the first Transaction by client.

Thanks for your help!

Cheers

[Ashish_Mathur]

Hi,

You may download my PBI file from here.

Hope this helps.

[v-alq-msft · Accepted solution]

Hi, @Vincem35 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Calendar(a calculated table):

Calendar = CALENDARAUTO()

There is a relationship between two tables. You may create a measure as below.

Result = 
var tab = 
ADDCOLUMNS(
    SUMMARIZE(
        ALLSELECTED('Table'),
        'Table'[Client ID],
        "MinDate",
        MIN('Table'[Transaction Date])
    ),
    "S",
    CALCULATE(
        SUM('Table'[Sales]),
        FILTER(
            ALL('Table'),
            'Table'[Client ID]=EARLIER('Table'[Client ID])&&
            'Table'[Transaction Date]=EARLIER([MinDate])
        )
    )
)
return
SUMX(
    tab,
    [S]
)

Result:

Best Regards

Allan

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

[amitchandak]

@Vincem35 , Check Page 2 in the file attached after signature

[Vincem35]

Thanks a lot!
It works well when i set the date as a slicer but i would need to have it in the formula as it is dynamic - Any thoughs?

Thanks again

[amitchandak]

@Vincem35 , can you explain with an example