topic DAX: Top Customer by Sales on a specific date in DAX Commands and Tips

DAX: Top Customer by Sales on a specific date

adam_mac — Mon, 29 Mar 2021 14:22:06 GMT

Hi, i am trying to create a matrix table that shows the top 1 customer by the purchase date. I have created the below statements that returns the max date with sales (there is not always sales on every day) and then a sum statement filtering on this max date.

However when i create a matrix and try to filter by this statement it returns all the sales instead of just the sales on that day.

Does anyone know a statement i can write to just filter on the Top 1 customer by NetValue by the max date i created? I created similar tables for month and week...however this was just using the filter tab.

MaxDate_withSales = CALCULATE(MAX(Finance_Sales[PurchaseDate]),Finance_Sales[NetValueBasedOnCommitment]>0)

Daily_Order_Value = CALCULATE(SUM(Finance_Sales[NetValueBasedOnCommitment]),DATESBETWEEN(Finance_Sales[PurchaseDate],[MaxDate_withSales],[MaxDate_withSales]))

Re: DAX: Top Customer by Sales on a specific date

Jihwan_Kim — Mon, 29 Mar 2021 15:05:48 GMT

Hi, adam_mac

If it is OK with you, please share your sample pbix file, then I can try to look into it to come up with more accurately written measures.

Thank you.

Hi, My name is Jihwan Kim.

If this post helps, then please consider accept it as the solution to help other members find it faster.

Re: DAX: Top Customer by Sales on a specific date

adam_mac — Mon, 29 Mar 2021 15:07:27 GMT

hi Jihwan_Kim unfortunately this wont be possible as it contains confidential customer data.

Re: DAX: Top Customer by Sales on a specific date

Anonymous — Mon, 29 Mar 2021 17:21:19 GMT

// Assume that the field Customer Name from // the dimension Customers has been dropped // on the canvass of the table/matrix visual // together with all the accompanying measures // or attributes. // This measure will return 1 for the customer(s) // that have the highest order value on the last // date with sales. The last day with sales is // RELATIVE to the last day visible in the current // context. Of course, this measure honors all the other // existing filters. [Is Top Customer Within Max Sales Date?] = var vOneCustomerVisible = HASONEVALUE( Customers[CustomerID] ) var vLastVisibleDay = MAX( Dates[Date] ) var vLastDayWithSales = // vLastDayWithSales is dependent on the selections // you've made in all dimensions. So, if there are // 3 customers selected only, the last sales day will be // the last sales day for those 3 customers. This will // most likely change if you select more customers or // have no filters on customers at all. This, of course, // can be changed do give you the absolute last sales // day but it's not clear from your description what the // last sales day should be. MAXX( // Dates must be a date table marked // as a date table in the model. CALCULATETABLE( SUMMARIZE( Sales, // fact table Dates[Date] ), Dates[Date] <= vLastVisibleDay ), Dates[Date] ) var vIsBestCustomer = if( vOneCustomerVisible, var vCurrentCustomer = SELECTEDVALUE( Customers[CustomerID] ) var vBestCustomers = SELECTCOLUMNS( TOPN(1, ALLSELECTED( Customers ), CALCULATE( // [Order Value] is the basic measure // by which you want to order customers. [Order Value], Dates[Date] = vLastDayWithSales ), DESC ), "@BestCustomerID", Customers[CustomerID] ) return vCurrentCustomer in vBestCustomers ) return 1 * vIsBestCustomer

Re: DAX: Top Customer by Sales on a specific date

adam_mac — Tue, 30 Mar 2021 08:51:26 GMT

thanks Anonymous , are you able to adapt this code so that sales day is absolute? Last sales day should equal where Order value <> zero i.e. where order value is greater than zero.