Check your eligibility for this 50% exam voucher offer and join us for free live learning sessions to get prepared for Exam DP-700.
Get StartedDon't miss out! 2025 Microsoft Fabric Community Conference, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount. Prices go up February 11th. Register now.
This is 3rd time , I am asking same help" to resolve the issue",can you help me , I did my best but not working ... how to calculate sum of last date sales by customer if we have diffrent last date by customers
sample table
Customers Id Date Amount
KSF-001 01-01-20 20000
KSF-002 02-01-20 50000
KSF-001 01-31-20 15000
KSF-003 05-23-20 5000
KSF-001 02-11-20 25000
KSF-002 03-01-20 60000
KSF-002 04-01-20 25000
Total 200000
Need solution like this
Customers Id Date Amount
KSF-003 05-23-20 5000
KSF-001 02-11-20 25000
KSF-002 04-01-20 25000
Total 55000 Regards Faslin
Solved! Go to Solution.
Please make these two measures (replacing with your table and column name). Use the 2nd one in your table visual with Customer Id column, to get your desired result.
Last Date Amount =
CALCULATE (
SUM ( Data[Amount] ),
TOPN ( 1, VALUES ( Data[Date] ), Data[Date], DESC )
)
Sum of Last Dates Each Customer =
SUMX ( VALUES ( Data[Customer Id] ), [Last Date Amount] )
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
Please make these two measures (replacing with your table and column name). Use the 2nd one in your table visual with Customer Id column, to get your desired result.
Last Date Amount =
CALCULATE (
SUM ( Data[Amount] ),
TOPN ( 1, VALUES ( Data[Date] ), Data[Date], DESC )
)
Sum of Last Dates Each Customer =
SUMX ( VALUES ( Data[Customer Id] ), [Last Date Amount] )
If this works for you, please mark it as the solution. Kudos are appreciated too. Please let me know if not.
Regards,
Pat
To learn more about Power BI, follow me on Twitter or subscribe on YouTube.
superb...Thank you soo much , much appriciated
Regards
Faslin
Hi @Anonymous,
Seeing that you ask for a power query solution to that, what about grouping?
You could try this:
#"Grouped Rows" = Table.Group(PreviousStep, {"Customers Id"}, {{"Date", each List.Max([Date]), type date}, {"Amount", each let d = List.Max([Date]) in List.Sum(Table.SelectRows(_, each [Date] = d)[Amount]), type number}})
Cheers,
smauro
DAX problems are all the same - they are filtering problems. I would approach this as follows
1. Create a star schema (add a customer table and a date table, join 1 to many to your data table)
2. Create a visual with customer id from the customer table, date from the date table
3. write the measures
last sale date = max(calendar[date])
sales on last date =calculate(sumx(customerTable,calculate(sum(fact[amount]))),Filter(calendar,calendar[date]=[last sale date])
or possibly as follows (you would need to test them)
=sumx(customerTable,calculate(sum(fact[amount]),Filter(calendar,calendar[date]=[last sale date])))
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
14 | |
13 | |
13 | |
12 | |
12 |