Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Don'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.

Reply
Anonymous
Not applicable

how to calculate sum of last date sales by customer if we have different last date by customers

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 

1 ACCEPTED SOLUTION
mahoneypat
Microsoft Employee
Microsoft Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

4 REPLIES 4
mahoneypat
Microsoft Employee
Microsoft Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

superb...Thank you soo much , much appriciated 

 

 

Regards

Faslin 

 

Smauro
Solution Sage
Solution Sage

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




Feel free to connect with me:
LinkedIn

MattAllington
Community Champion
Community Champion

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])))

 

 



* Matt is an 8 times Microsoft MVP (Power BI) and author of the Power BI Book Supercharge Power BI.
I will not give you bad advice, even if you unknowingly ask for it.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.

Jan25PBI_Carousel

Power BI Monthly Update - January 2025

Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.

Jan NL Carousel

Fabric Community Update - January 2025

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