Forum Discussion

abloor's avatar
abloor
Icon for Helper IV rankHelper 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: https://1drv.ms/u/s!AvZLa7VG1kXrghL-VePQJrtq06qC?e=hhMgQN   The first tab (Country)...
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.

v-zhenbw-msft's avatar
v-zhenbw-msft
Icon for Community Support rankCommunity 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
    Icon for Helper IV rankHelper 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
      Icon for Community Support rankCommunity 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.

      • abloor's avatar
        abloor
        Icon for Helper IV rankHelper IV
        6 years ago

        Perfect! That's just what I need, thank you very much v-zhenbw-msft .

         

        Another quick question...The column 2 seems to have the same data as Country column (column 3).

        Can you please explain to me why the 3rd column is needed so I understand?

         

        Thanks again for all the time you put into this for me.  I greatly appreciate it 😀