DAX - Apply Last Value by Date to Entire Dimension

[Anonymous]

Hello everyone,

Can you help me figure out how I can achieve the following in DAX ?

• I have Customer Number and Customer Name. This information is listed by Snapshot Date.
• Customer Name can change but Customer Number always remains the same forever.

==> I want to pick up the last Customer Name value and have it applied to the whole dimension.

The name of this measure will be named Current Customer Name as opposed to the "Historical Customer Name":

Example:

Date    Cust Nbr   Cust Name        Current Cust Name

Jan 1    1234          World Inc          World Corp

Jan 2    1234          World Inc          World Corp

Jan 3    1234          World Corp       World Corp

Jan 4    1234          World Corp       World Corp

Jan 5    1234          World Corp       World Corp

Thanks in advance.

Jason

[ryan_mayu · Accepted solution]

@Anonymous

You can try below codng to see if this can solve your problem. Thanks

Column = 
VAR maxdate=MAXX(FILTER('Sheet14',Sheet14[customernumber]=EARLIER(Sheet14[customernumber])),'Sheet14'[date])
return MAXX(FILTER('Sheet14',Sheet14[customernumber]=EARLIER(Sheet14[customernumber])&&'Sheet14'[date]=maxdate),Sheet14[name])

[Anonymous]

@ryan_mayu

This is amazing !

It works.

Just a question, why does it have to be a column and not a measure ?

Thanks,

Jason.

[ryan_mayu]

@Anonymous

You are welcome. The first formula came out of my mind to solve this kind of problem is Eariler. Earlier can only be used in column calculation. Maybe others can do that in measure. However, I am also new to PBI. My knowledge is still limited and I am also learning DAX. 🙂