Help - date range lookup between queries

[javieezy]

Dear all, 

I have 2 tables in 2 queries: 

-1: Period table: 

-2: Data table: 

What I need is: 

-Depends on the Columna "SimpleDateLogOff" in Table 2, Add a column in Table 2 that returns the value of the Column "period" of Table 1. 

*Example: "SimpleDateLogOff" is 1/3/2022, which is between 12/12/2021 - 1/8/2022, hence should return "1" of "Periodo" column. 

Many thanks in advance, appreciate it! 

[v-yalanwu-msft]

Hi, @javieezy ;

Or you could in power query. first list.dates the merge it.

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-yalanwu-msft]

Hi, @javieezy ;

You could create a measure or column by dax.

Measure.

Measure = CALCULATE(MAX('Period'[Periodo]),FILTER('Period',MAX('Data'[SimpleDateLogoff]) in CALENDAR([Start],[Finish])))

Column.

Column = CALCULATE(MAX('Period'[Periodo]),FILTER('Period','Data'[SimpleDateLogoff] in CALENDAR([Start],[Finish])))

The final show:

Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[javieezy · Accepted solution]

Found a way: 

Add a custom column in Period Table using “List.Dates" function, to have all the dates between a period. 

Then just merge 2 tables.