cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

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

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.

Cheers

1 ACCEPTED SOLUTION
Community Support

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 =
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.

5 REPLIES 5
Super User

Hi,

Hope this helps.

Regards,
Ashish Mathur
http://www.ashishmathur.com
Community Support

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 =
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.

Super User

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

Helper I

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

Super User

@Vincem35 , can you explain with an example

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors