英文:
PowerBI DAX to lookup in another table between two dates
问题
你好,以下是您要翻译的内容:
"Hello all you beautiful people!
With lots of googling- still cannot solve this simple PowerBI trick. I want to build another table based on two tables - for timesheets - of which one table has the start and end dates of the timesheets month, and the other the timesheets themselves. I want to get a column added to the timesheet to state in which timesheet month it falls. (Timesheet month end is a few days before month's last date, based on day of the week, etc)
Example tables:
Table cfgTimesheetMnth:
Period | tsDateStart | tsDateEnd |
---|---|---|
202302 | 2023-01-28 | 2023-02-24 |
202303 | 2023-02-25 | 2023-03-29 |
202304 | 2023-03-30 | 2023-04-26 |
Table erpPrjTimesheets :
EmpNo | tsDate | Hrs |
---|---|---|
100101 | 2023-03-05 | 7 |
100101 | 2023-03-30 | 6 |
Results Table. I then want to build a new table in PowerBI that needs to look as such:
Table verpPrjTimesheets:
EmpNo | tsDate | Hrs | tsPeriod |
---|---|---|---|
100101 | 2023-03-05 | 7 | 202303 |
100101 | 2023-03-30 | 6 | 202304 |
The code in the dataview in PowerBI I started with are:
VAR t1 = SELECTCOLUMNS(erpPrjTimesheets
, "EmpNo" , [EmpNo]
, "tsDate" , [tsDate]
, "Hrs" , [Hrs]
, "tsPeriod" , LOOKUPVALUE(cfgTimesheetsMnth[Period], cfgTimesheetsMnth[tsDateEnd], erpPrjTimesheets[tsDate])
) RETURN
t1
But the LOOKUPVALUE does not have a command to lookup between two date ranges. So above does not work.
What would be a better approach?"
英文:
Hello all you beautiful people!
With lots of googling- still cannot solve this simple PowerBI trick. I want to build another table based on two tables - for timesheets - of which one table has the start and end dates of the timesheets month, and the other the timesheets themselves. I want to get a column added to the timesheet to state in which timesheet month it falls. (Timesheet month end is a few days before month's last date, based on day of the week, etc)
Example tables:
Table cfgTimesheetMnth:
Period | tsDateStart | tsDateEnd |
---|---|---|
202302 | 2023-01-28 | 2023-02-24 |
202303 | 2023-02-25 | 2023-03-29 |
202304 | 2023-03-30 | 2023-04-26 |
Table erpPrjTimesheets :
EmpNo | tsDate | Hrs |
---|---|---|
100101 | 2023-03-05 | 7 |
100101 | 2023-03-30 | 6 |
Results Table. I then want to build a new table in PowerBI that needs to look as such:
Table verpPrjTimesheets:
EmpNo | tsDate | Hrs | tsPeriod |
---|---|---|---|
100101 | 2023-03-05 | 7 | 202303 |
100101 | 2023-03-30 | 6 | 202304 |
The code in the dataview in PowerBI I started with are:
verpPrjTimesheets =
VAR t1 = SELECTCOLUMNS(erpPrjTimesheets
, "EmpNo" , [EmpNo]
, "tsDate" , [tsDate]
, "Hrs" , [Hrs]
, "tsPeriod" , LOOKUPVALUE(cfgTimesheetsMnth[Period], cfgTimesheetsMnth[tsDateEnd], erpPrjTimesheets[tsDate])
) RETURN
t1
But the LOOKUPVALUE does not have a command to lookup between two date ranges. So above does not work.
What would be a better approach?
答案1
得分: 1
这是翻译好的部分:
verpPrjTimesheets =
ADDCOLUMNS(
erpPrjTimesheets,
"tsPeriod",
SELECTCOLUMNS(FILTER(cfgTimesheetMnth, erpPrjTimesheets[tsDate] >= cfgTimesheetMnth[tsDateStart] && erpPrjTimesheets[tsDate] <= cfgTimesheetMnth[tsDateEnd]), "tsPeriod", cfgTimesheetMnth[Period])
)
英文:
Here you go.
verpPrjTimesheets =
ADDCOLUMNS(
erpPrjTimesheets,
"tsPeriod",
SELECTCOLUMNS(FILTER(cfgTimesheetMnth, erpPrjTimesheets[tsDate] >= cfgTimesheetMnth[tsDateStart] && erpPrjTimesheets[tsDate] <= cfgTimesheetMnth[tsDateEnd]), "tsPeriod", cfgTimesheetMnth[Period])
)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论