DAX - How to find the latest date based the Customer IDs belonging to a single Customer Name

[Anonymous]

Hi,
I have Dim_Customer table with the following:
PK_Cust_ID              Cust_Name
111                          TKL Supplies
112                          TKL Supplies
113                          TKL Supplies
289                          OCCAs
29900                      OCCAs

The Dim_Customer.PK_Cust_ID column joins to the Fact.FK_Cust_ID table. The Fact table looks like this:
FK_Cust_ID               Date                        Sales         Depot
111                           01/02/2019            122.00       22
111                           20/03/2019            8.00           22
112                           01/01/2019            28.00         22
111                           23/05/2019            61.00         28
289                           20/05/2019            88.00         22
113                           20/02/2019            112.00       89

I should say there is no slicer placed on the Cust_Name in the report. The only context filtering used is the Cust_Name column within a Matrix visiual.

What I need to be able to calcuate is the latest (last) date for the Cust_Name regardless of what Cust_IDs the Cust_Name may have, or the Depots the transaction is recorded against.

For example, in the Matrix visual, for the row where the Cust_Name is TKL Supplies I need the measure to return the 23/05/2019 as this is the latest date present for the TKL Supplies customer, regardless of the customer's Cust_ID or Depot.

How do I write this measure?
Of course, the measure will need to work out the same for every customer (Cust_Name) so the measure will need to take into account the Matrix visual's context filtering.

Thanks.

[Anonymous · Accepted solution]

I guess that there is a one-way relationship between your DIM and your FACT. So when you put your fact in your matrix, the DIM is not filtered against, therefore there is no Filter context applied.
The weird aspect is that you have several Customer IDs for the same Customer Name...

First of all, for simplicity I would add the Cust_name in the fact with a custom column
Cust_Name=RELATED(Dim_Customer[Cust_Name]) 

Then another custom column (or a measure if the other filters changes context...)
LastDate=
VAR thisCustName=SELECTEDVALUE(Fact[CustName])

RETURN
MAXX(FILTER(Dim_Customer;Dim_Customer[Cust_Name]=thisCustName);Dim_Customer[Date])

The MAXX iterates along all of the filtered rows and choose the max value.

Should work

[Anonymous]

@Anonymous 

Thanks for your response. I shall attempt to implement it now.

This is what I have tried in the mean time, without joy. Can you see what I'm thinking in my attempt and could it work with the right tweaks (what are they)?
(the table/field names have changed slightly from my initial post)

WEEKS SINCE LAST TRANSACTION AT ALL DEPOTS =

VAR __Customer = SELECTEDVALUE ( Dim_Customer[PK_CustomerID] )

VAR __MaxDate =

CALCULATE(

                         LASTDATE( 'Fact'[PostDate] )

                        , ALL( Dim_Depot[PK_DepotID])

                        , CALCULATETABLE( Dim_Customer

                                                               , ALL( Dim_Customer )

                                                               , Dim_Customer[K8 Customer Name] = __Customer

                                                             )

                        )

RETURN __MaxDate

[Anonymous]

A calculateTable *inside* a Calculate with a Few ALL() around...you like headaches, don't you? 🙂
That means two context transitions plus a couple of ALL that modify filters...

First of all the CALCULATETABLE is needed when you have relationships, but on the one-side. What you want to do (i guess) with this piece:

CALCULATETABLE( Dim_Customer
                                                               , ALL( Dim_Customer )
                                                               , Dim_Customer[K8 Customer Name] = __Customer
                                                             )

Is to find all customers of your row one. But you are working within the same table so there's no need to invoke calculatetable as there's no context transition.

Also LASTDATE does not return a "max" date, but the LASTDATE in a given month (i.e. LASTDATE(14-03-2019) will return 31-03-2019)

Honestly I don't know what are the fixes, because again a CALCULATETABLE within a CALCULATE with to ALLs...is complicated to visualize

[Anonymous]

@Anonymous 
Thanks again. Your solution worked. I just needed to added an ALL() clause on the Depot and all was perfect.
Still interested to know if my attempted (code snippet) in the previous post was close to working and could it work with some minor tweaks?