Forum Discussion

abloor's avatar
abloor
Helper IV
6 years ago
Solved

Find most recent value

Hi,
Can I please get some help in creating a column that uses a lookup to find a value?
 
See some data here:
 
The first tab (Country) shows the product sales for 2013/2014 and which country the product was made in.
Note that Velo changes its country to France from Oct 2014 onwards.
And VTT changes its country to Mexico from Jan 2014 onwards.
 
The second tab (Budget) shows the revenue budgets assigned to each product by month.  In this table I want to create a Country calculated column using DAX that says what the Country was for each product for the relevant date.
For Paseo, Montana, Carretana, the country will be consistent for each budget date as they never changed.
But for Velo and VTT, I expect to see the Country change to match the month.

And for example, some products didn't have a sale for every single month, so the Lookup may not be able to find the relevant data...in this case how can I tell it to use the most recent known Country?
e.g. for VTT in Jan-Aug 2013 there were no sales, so I'd like it to tell me the last known country.
 
Thanks for your time!
Need Help
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    Hi abloor ,

     

    We can create three columns in Budget table.

     

    Column = CALCULATE(MAX(Country[Country]),FILTER(Country,Country[Product]=Budget[Product] && Country[Date]=Budget[Date]))

     

    Column 2 = 
var _min_date = CALCULATE(MIN(Budget[Date]),FILTER(Budget,Budget[Product]=EARLIER(Budget[Product])&&Budget[Column]<>BLANK()))
return
CALCULATE(MAX(Budget[Column]),FILTER(Budget,Budget[Product]=EARLIER(Budget[Product]) && Budget[Date]=_min_date))

     

    Country column = 
IF(
    ISBLANK(Budget[Column]),Budget[Column 2],Budget[Column])

     

     

    If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

     

    Best regards,

     

    Community Support Team _ zhenbw

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

     

    BTW, pbix as attached.

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    abloor , The information you have provided is not making the problem clear to me. Can you please explain with a Data example.
    Also there no country in budget data.
    Appreciate your Kudos.


    • abloor's avatar
      abloor
      Helper IV
      6 years ago

      Thank you for your time amitchandak 

       

      I have revised the data file to include a 3rd tab which shows the expected data in the calculated column I need help with. 

      https://1drv.ms/u/s!AvZLa7VG1kXrghL-VePQJrtq06qC?e=hhMgQN

       

      The Country column in the Budget tab is intentionally blank as that is the calculated column with Lookup dax I need help to create in PBI.

       

      I want to fill that column with the correct country as per the matching dates on the Country and Budget tab, for each product.

       

      Hopefully that makes it clearer? Thank you.

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Community Support
    6 years ago

    Hi abloor ,

     

    We can use the Power Query Editor to meet your requirement.

     

    1. Reference the Country table, then Group country, product and Date column.

     

     

     

    2. Merge the new country table and Budget table.

     

     

    3. Expand the country column and fill up the column.

     

     

     

    if it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

     

    Best regards,

     

    Community Support Team _ zhenbw

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

     

    BTW, pbix as attached.

    • abloor's avatar
      abloor
      Helper IV
      6 years ago

      Thank you v-zhenbw-msft  for the time you have spent on this for me so far.

      I've tried to implement your solution with my real company data, but I found when I filled 'up' the expanded column, it didn't fill it with the correct Country.  I tried ordering the columns prior to this so the data was organised correctly, but it didn't work unfortunately. I also tried fill 'down', to no avail.

       

      I have uploaded a new version of the excel table to the link below to hopefully make things clearer.  Please see tab 3 Budget Expectation.

      I have also uploaded a PBIX file which shows the solution I had been trying to do, prior to doing this forum post.  You can see I've concatenated the Product and Date columns in each table to create a unique reference.  Then I used these concatenated columns in a LOOKUPVALUE to created the CountryCalc column in the Budget table.

       

      See how it partially pulls in the correct data for me, however there are some blanks where it can't find the data needed in the Country table.  That is because a sale wasn't made for that product in that month so their isn't a row for this data in the Country table.

       

      So I was hoping to be able to add a second line in the CountryCalc DAX that tells PBI to find the last known country for that product if blank.

       

      https://1drv.ms/u/s!AvZLa7VG1kXrghL-VePQJrtq06qC?e=e1fhXm 

       

      Thanks very much 

      • v-zhenbw-msft's avatar
        v-zhenbw-msft
        Community Support
        6 years ago

        Hi abloor ,

         

        We can create three columns in Budget table.

         

        Column = CALCULATE(MAX(Country[Country]),FILTER(Country,Country[Product]=Budget[Product] && Country[Date]=Budget[Date]))

         

        Column 2 = 
var _min_date = CALCULATE(MIN(Budget[Date]),FILTER(Budget,Budget[Product]=EARLIER(Budget[Product])&&Budget[Column]<>BLANK()))
return
CALCULATE(MAX(Budget[Column]),FILTER(Budget,Budget[Product]=EARLIER(Budget[Product]) && Budget[Date]=_min_date))

         

        Country column = 
IF(
    ISBLANK(Budget[Column]),Budget[Column 2],Budget[Column])

         

         

        If it doesn’t meet your requirement, could you please show the exact expected result based on the table that you have shared?

         

        Best regards,

         

        Community Support Team _ zhenbw

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

         

        BTW, pbix as attached.