Returning a Value Using LASTNONBLANK

[chudson]

Hi,

I have a table that has SalesRep and associated Sales Manager at the time of an order in my FactSales table.  My FactSales table is sorted from oldest to most recent order date and I'm trying to return a calculated column in the SalesRep Master table that pulls in their current Sales Manager.  I've tried various calculations (one example below) but when there are multiple values for the Sales Manager to a rep it is not returning the newest value it seems to return the oldest instead of the newer Sales Manager.  Can anyone help me out?  

 CALCULATE(LASTNONBLANK(FactSales[RBM],MAX(FactSales[RevenueRecognitionDate])),FILTER(ALL(FactSales),FactSales[RepID]=DimSurgeon[RepID]))

Thanks,

[Cmcmahan · Accepted solution]

It might be sort of a flippant reply, but if LASTNONBLANK is getting you the oldest manager and you want the newest manager, would FIRSTNONBLANK work?

I would use TOPN and create this as a measure. You can change it to use OrderNum instead of OrderDate, if that's a better indicator of "current". Sample .pbix attached.

CurrentManager = CALCULATE( SELECTEDVALUE('Dim Sales Rep'[SalesManagerCode]), TOPN( 1, 'Dim Sales Rep', 'Dim Sales Rep'[Order Date], DESC))

[Cmcmahan · Accepted solution]

It might be sort of a flippant reply, but if LASTNONBLANK is getting you the oldest manager and you want the newest manager, would FIRSTNONBLANK work?

I would use TOPN and create this as a measure. You can change it to use OrderNum instead of OrderDate, if that's a better indicator of "current". Sample .pbix attached.

CurrentManager = CALCULATE( SELECTEDVALUE('Dim Sales Rep'[SalesManagerCode]), TOPN( 1, 'Dim Sales Rep', 'Dim Sales Rep'[Order Date], DESC))

[Anonymous]

Can  you upload some sample data and what you are looking for?

But a few quick notes:

1. Sort order of the table has no affect.

2. Everything happens in context, be it a filter context and/or row context

3. You never want to use FILTER on a fact table. Use filter on the smaller dimension tables 

4. Do not add a calculated column to a fact table, that's a recipe for bad performance

[chudson]

Hi @Anonymous ,

Below is some dummy data showing what I'm trying to accomplish.  I'm trying to create the calculated column in the Dim Sales Rep Table not a fact table.  I am referencing the fact table though.

As you can see I want to bring in the SalesManagerCode to the Dim Sales Rep table based on the most recent order date.  When I use the calculated column above it returns the earlier manager code instead of the newest (use rep 101 for this example).  Let me know if this helps explain the issue and what I'm trying to accomplish.

OrderNum	Order Date	SalesRepCode	SalesManagerCode
1234	7/31/2019	101	201
1235	8/1/2019	102	401
1236	8/2/2019	102	401
1237	8/5/2019	103	501
1238	8/27/2019	101	301
1239	8/27/2019	101	301

SalesRepCode	CurrentManager Result	CurrentManagerCode Expected
101	201	301
102	401	401
103	501	501

Thanks,

[dax · Accepted solution]

Hi chudson, 

You also could try below measure to see whether it works or not

Measure 5 = CALCULATE(Min('Sales Rep'[SalesManagerCode]), FILTER(ALLEXCEPT('Sales Rep','Sales Rep'[SalesRepCode]),'Sales Rep'[Order Date]=MAX('Sales Rep'[Order Date])))

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.