topic Sum Sales For Last Transaction Date by Customer, Over last 3 Months in DAX Commands and Tips

Sum Sales For Last Transaction Date by Customer, Over last 3 Months

Menar — Mon, 01 May 2023 18:07:20 GMT

Hi everyone !

I am pretty to new to DAX and heavily struggling with this.

I am aiming for a measure that calculates the sum of sales by Customerid only for their last transaction date in the last 3 months.
The last 3 months should depend on the dates filtered. If I select the 20/01/2023 in my date slicer, the measure should scan the period from 20/01/2023 to 20/10/2022 and Sum the Sales for each Customerid for their Last Date in this period.

I still need to be able to slice the data by other fields, like ProductId or Salesagentid.

Here's my sample data :

Customerid	Salesagentid	ProductId	Transaction Date	Sales
456	300502	1	01/11/2022	400
456	300502	3	01/11/2022	950
456	300502	7	01/11/2022	900
322	300897	7	05/11/2022	800
322	300897	8	05/11/2022	150
322	300897	1	05/11/2022	50
322	300897	6	05/11/2022	400
111	300700	7	15/11/2022	150
111	300700	3	15/11/2022	600
111	300700	5	15/11/2022	350
322	300897	4	12/12/2022	550
322	300897	1	12/12/2022	700
456	300564	9	28/12/2022	350
456	300564	3	28/12/2022	600
111	300700	8	31/12/2022	850
111	300700	7	31/12/2022	200
111	300700	9	31/12/2022	350
111	300700	8	25/01/2023	50
111	300700	7	25/01/2023	950
111	300700	9	25/01/2023	800
322	300897	8	30/01/2023	850
322	300897	9	30/01/2023	700
322	300897	4	30/01/2023	0
456	300564	3	02/02/2023	50
456	300564	1	02/02/2023	850
456	300564	8	02/02/2023	200
456	300564	4	02/02/2023	150

Expected Result If I filter on the 26th of january 2023 :

Customerid	Salesagentid	ProductId	Transaction Date	Sales
322	300897	4	12/12/2022	550
322	300897	1	12/12/2022	700
456	300564	9	28/12/2022	350
456	300564	3	28/12/2022	600
111	300700	8	25/01/2023	50
111	300700	7	25/01/2023	950
111	300700	9	25/01/2023	800

The logic is that I only want to show the latest data depending on dates filtered (last date over the last 3 months from max date filtered) for each Customerid.

I did try some measures using DATESINPERIOD, LASTDATE and SUMX but none of that really worked.

Link to Excel file used as source date :
https://www.dropbox.com/scl/fi/sqtgaait9x0psmj7eg72n/Database-sum-sales-by-cust-for-max-date.xlsx?dl=0&rlkey=9vef4v1c89uh6b2elli1978d5

Link to the PBI file :
https://www.dropbox.com/s/owl1jvjng4h4ik0/Sum%20sales%20per%20customerid%20for%20max%20date.pbix?dl=0

The data model :

Thank you so much in advance for any help, would be much appreciated !

Re: Sum Sales For Last Transaction Date by Customer, Over last 3 Months

lbendlin — Wed, 03 May 2023 00:06:28 GMT

Thank you for providing the sample data. We may have a slight disagreement on the date format though. Here's my result for Jan 26 2023:

You can't use a connected dates table for this exercise.

see attached.

Re: Sum Sales For Last Transaction Date by Customer, Over last 3 Months

Menar — Wed, 03 May 2023 11:24:19 GMT

Thank you for your response.

Yes, I am aiming for something like this but the measure should only returns the data from the latest transaction date per customer.
Meaning on Jan 26 2023 filtered, I should only see the data from Jan 25 for customerid 111, from Dec 28 2022 for customerid 456 and from Dec 12 2022 for customerid 322.

Re: Sum Sales For Last Transaction Date by Customer, Over last 3 Months

lbendlin — Wed, 03 May 2023 12:20:03 GMT

Show = var m = max(Dates[Date]) var d = CALCULATE(max('Table'[Transaction Date]),ALLEXCEPT('Table','Table'[Customerid]),'Table'[Transaction Date]<=m && 'Table'[Transaction Date]>= EDATE(m,-3)) return if (SELECTEDVALUE('Table'[Transaction Date])=d,1,0)

Re: Sum Sales For Last Transaction Date by Customer, Over last 3 Months

Menar — Wed, 03 May 2023 19:02:39 GMT

I see, it does work in the layout you provided, thanks, but it does not intereact with any dates slicer which would come from a date table (because there is no relationship obviously).

My user has to be able to filter dates on the report through a date slicer ☹️

Plus it seems i can not apply the filter for "Show =1" to the whole report, it has to be done visual by visual.

Re: Sum Sales For Last Transaction Date by Customer, Over last 3 Months

lbendlin — Wed, 03 May 2023 19:31:30 GMT

My user has to be able to filter dates on the report through a date slicer :frowning_face:

They can still do that, but the slicer has to be fed from the disconnected table.

Measures can only serve as visual filters. If this is important to you please consider voting for an existing idea or raising a new one at https://ideas.powerbi.com

Re: Sum Sales For Last Transaction Date by Customer, Over last 3 Months

Menar — Thu, 04 May 2023 15:58:09 GMT

I see, that's not exactly what i was looking for but thank you for your help!

I'll keep looking to see if this achievable.