Forum Discussion

Ronie's avatar
Ronie
Frequent Visitor
10 years ago
Solved

LOOKUPVALUE returns blank value

Hello,  Following the recent help of Jahida (thanks again!), I managed to do some good progress with the dashboard I'm preparing for my boss. Unfortunately, I'm facing a new problem, related to LOOK...
Need Help
  • v-haibl-msft's avatar
    v-haibl-msft
    10 years ago

    Ronie

     

    Please create a new column in Time table with following formual.

    Time_Format =
FORMAT ( 'Time'[Time], "Long Time" )

    Then update the Shift column formula as below. It should can resolve the problem.

    Shift = 
 LOOKUPVALUE ( Time[Shift], 'Time'[Time_Format], FORMAT( Yearly[Time(Adj)], "Long Time" ) )

    The root cause is that some date of Time(Adj) are 12/31 but all date in Time(Time) are 12/30.

     

    Best Regards,

    Herbert

Ronie's avatar
Ronie
Frequent Visitor
10 years ago

Alright!
Sorry Herbert, I had to remove a lot of things from the file and which was breaking it, so I took me a bit longer than expected.

 

You can find the pbix here:
https://1drv.ms/f/s!AtMrshF_PQK9gRFxCHmOA3Xl3TJP

 

The table will probably look empty at first as I didn't clean the file properly. But if you right click on [Time] in the 'Yearly' table and sort descending, you should be able to see the issue I described in the column [Shift].


Thanks again for your help!

-Ronie

 

 

v-haibl-msft's avatar
v-haibl-msft
Microsoft Employee
10 years ago

Ronie

 

Please create a new column in Time table with following formual.

Time_Format =
FORMAT ( 'Time'[Time], "Long Time" )

Then update the Shift column formula as below. It should can resolve the problem.

Shift = 
 LOOKUPVALUE ( Time[Shift], 'Time'[Time_Format], FORMAT( Yearly[Time(Adj)], "Long Time" ) )

The root cause is that some date of Time(Adj) are 12/31 but all date in Time(Time) are 12/30.

 

Best Regards,

Herbert

  • Ronie's avatar
    Ronie
    Frequent Visitor
    10 years ago

    v-haibl-msft

    Thank you very much! Works like a charm!