Forum Discussion
Grab Value Other Table Between 2 Dates
I have two tables, and trying to grab the Period Name column into the main table based on being between the two dates
Table FinPeriod
Start Date | End Date | Period Name
2022-03-01 | 2022-04-10 | 2022-Mar
2022-04-11 | 2022-05-02 | 2022-Apr
2022-05-03 | 2022-05-31 | 2022-May
Table TimeEntry
Employee | Date | Hours
1213 | 2022-04-05 | 8
1713 | 2022-03-05 | 7
1089 | 2022-04-19 | 5
Would like to see the data in the following manner.
Employee | Date | Hours | Period Name
1213 | 2022-04-05 | 8 | 2022-Mar
1713 | 2022-03-05 | 7 | 2022-Mar
1089 | 2022-04-19 | 5 | 2022-Apr
I thought it would be possibly to do something like this but no success.
Column = CALCULATE(FinPeriod[Period Name],FILTER(ALL(FinPeriod),FinPeriod[Start Date]>=TimeEntry[Date] && FinPeriod[End Date]<=TimeEntry[Date]))
Any help is greatly appricated!
- Anonymous4 years ago
HI hsvjumper,
You can take a look at the following blog 'start date', 'end date' part if helps:
In addition, you can also try to create a new table to expand date ranges and mapping to raw table records:
Solved: Spread revenue across period based on start and en... - Microsoft Power BI Community
Regards,
Xiaoxin Sheng
3 Replies
- AnonymousNot applicable
if you have common columns in the two tables then maybe you can merge the two tables.
I would also recommend that you use lookupvalue function to get the values from other table. - AnonymousNot applicable
As i understant, your period (month-yr) is based on start date so maybe you can pull the start date from the other table.
- AnonymousNot applicable
HI hsvjumper,
You can take a look at the following blog 'start date', 'end date' part if helps:
In addition, you can also try to create a new table to expand date ranges and mapping to raw table records:
Solved: Spread revenue across period based on start and en... - Microsoft Power BI Community
Regards,
Xiaoxin Sheng