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.
Hi,
I am working on a report that is supposed to show when our customers bought our products the last time and at which qty.
My table structure is as follows:
PRODUCT TABLE
Product |
PA |
PB |
PC |
CUSTOMER TABLE
Customer |
C1 |
C2 |
C3 |
SALES TABLE
Date | Product | Qty | Customer |
01.04.2020 | PA | 1 | C1 |
02.04.2020 | PA | 4 | C2 |
03.04.2020 | PB | 5 | C2 |
04.04.2020 | PC | 8 | C3 |
05.04.2020 | PA | 8 | C1 |
MATRIX VISUAL (desired outcome)
Customer | Product | Last Date | Last Qty |
C1 | PA | 05.04.2020 | 8 |
C1 | PB | ||
C1 | PC | ||
C2 | PA | 02.04.2020 | 4 |
C2 | PB | 03.04.2020 | 5 |
C2 | PC | ||
C3 | PA | ||
C3 | PB | ||
C3 | PC | 04.04.2020 | 8 |
It is also key that non-sales also show.
As I am not particularly proficient in writing DAX, I would deeply appreciate if I could get help with the formulas here.
Regards,
depple
Solved! Go to Solution.
Dear friend ,
You can try
Last_Date = MAXX(Sales table , Date )
If any doubt please let ke know
Appreciate by giving kudos and selecting this post as solution if it solved your problem
Regards
Sujit
Hi @depple ,
Assuming there are already relationships between table Sales and other two tables on field [Product] and [Customer] with Both of Cross filter direction, then you may create measures like DAX below.
Last Date =
CALCULATE (
MAX ( 'SALES'[Date] ),
ALLEXCEPT ( 'SALES', 'SALES'[Customer], 'SALES'[Product] )
)
Last Qty =
CALCULATE (
MAX ( 'SALES'[Date] ),
FILTER (
ALLEXCEPT ( 'SALES', 'SALES'[Customer], 'SALES'[Product] ),
'SALES'[Date] = 'SALES'[Last Date]
)
)
Then you may put 'PRODUCT'[Product] , 'CUSTOMER'[Customer] and two created measures into table visual, turn of the option "Show items with no data" for the 'PRODUCT'[Product] and 'CUSTOMER'[Customer] in Field tab.
For example:
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @depple ,
Assuming there are already relationships between table Sales and other two tables on field [Product] and [Customer] with Both of Cross filter direction, then you may create measures like DAX below.
Last Date =
CALCULATE (
MAX ( 'SALES'[Date] ),
ALLEXCEPT ( 'SALES', 'SALES'[Customer], 'SALES'[Product] )
)
Last Qty =
CALCULATE (
MAX ( 'SALES'[Date] ),
FILTER (
ALLEXCEPT ( 'SALES', 'SALES'[Customer], 'SALES'[Product] ),
'SALES'[Date] = 'SALES'[Last Date]
)
)
Then you may put 'PRODUCT'[Product] , 'CUSTOMER'[Customer] and two created measures into table visual, turn of the option "Show items with no data" for the 'PRODUCT'[Product] and 'CUSTOMER'[Customer] in Field tab.
For example:
Best Regards,
Amy
Community Support Team _ Amy
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-xicai ,
Thank you for your suggestion!
Sorry for taking so long coming back to you, but I have had major issus with the relationships in my model with the "Both" filter direction. It turned out impossible to have 2 x "Both", because a required data table is linked to both Customer and Product. With inspiration from your suggestions, I have now nearly worked my way around the issue.
I really appreciate your efforts in trying to help me.
Regards,
depple
@depple , Join both table sales Create
Max(Date)
And display it with show item with no data option for both dimension table
Thank you for trying to help me.
I do not understand what you mean by "Join both table sales". Could you please elaborate a little on that?
Regards,
depple
Dear friend ,
You can try
Last_Date = MAXX(Sales table , Date )
If any doubt please let ke know
Appreciate by giving kudos and selecting this post as solution if it solved your problem
Regards
Sujit
Hi @Sujit_Thakur,
Thank you for your suggestion, it appears to work perfectly!
Do you know how I could get the corresponding "Last_Qty" for "Last_Date"?
Regards
depple
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the January 2025 Power BI update to learn about new features in Reporting, Modeling, and Data Connectivity.
User | Count |
---|---|
133 | |
76 | |
53 | |
38 | |
37 |
User | Count |
---|---|
202 | |
80 | |
71 | |
55 | |
48 |