Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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...
Link to the PBI file :
https://www.dropbox.com/s/owl1jvjng4h4ik0/Sum%20sales%20per%20customerid%20for%20max%20date.pbix?dl=...
The data model :
Thank you so much in advance for any help, would be much appreciated !
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.
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)
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.
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
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.
User | Count |
---|---|
60 | |
22 | |
18 | |
18 | |
16 |
User | Count |
---|---|
86 | |
54 | |
54 | |
38 | |
21 |