Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
nankerp
Helper III
Helper III

Lookup - Interval - Periodes of dates

I have two two tables (main and lookup) and want to get a value (task) from the lookuptable depending on product and date.

 

I was thinking of using Power Query or datamodel.

 

Any suggestion for how to solve this problem.

 

Description of the problemDescription of the problem

1 ACCEPTED SOLUTION

I think I found a solution. Maybe not the most sexy one but it work.

 

SELECTCOLUMNS(
   topn(1;FILTER(factArbOppg;
    factArbOppg[Lisens]=EARLIER(factOverdragelse[Lisens])
    && factArbOppg[Frist]>=EARLIER(factOverdragelse[Dato])
    );
   factArbOppg[Frist];
   ASC);
  "NewColumn";factArbOppg[Oppgavenavn])

View solution in original post

2 REPLIES 2
AlB
Super User
Super User

Hi  @nankerp

 

What is end of period? End of the year?

If so, try this for your column in DAX, where Table2 is your Lookup table:

 

 

NewColumnTask =
LOOKUPVALUE (
    Table2[Task],
    Table2[Product], MainTable[Product],
    Table2[Date], ENDOFYEAR ( MainTable[Date] )
)

 

I think I found a solution. Maybe not the most sexy one but it work.

 

SELECTCOLUMNS(
   topn(1;FILTER(factArbOppg;
    factArbOppg[Lisens]=EARLIER(factOverdragelse[Lisens])
    && factArbOppg[Frist]>=EARLIER(factOverdragelse[Dato])
    );
   factArbOppg[Frist];
   ASC);
  "NewColumn";factArbOppg[Oppgavenavn])

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors