Last sale, qty and non-sales

[depple]

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

[Sujit_Thakur · Accepted 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

[v-xicai · Accepted solution]

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.

[depple]

@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

[amitchandak]

@depple , Join both table sales Create

Max(Date)

And display it with show item with no data option for both dimension table

[depple]

@amitchandak

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

[Sujit_Thakur · Accepted 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

[depple]

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