Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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
Solved! Go to 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.
Hi,
You may download my PBI file from here.
Hope this helps.
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.
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
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
126 | |
78 | |
76 | |
59 | |
51 |
User | Count |
---|---|
164 | |
86 | |
68 | |
68 | |
58 |