PowerBI DAX 在另一张表格中两个日期之间进行查找。

huangapple go评论91阅读模式
英文:

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.

PowerBI DAX 在另一张表格中两个日期之间进行查找。

verpPrjTimesheets = 
ADDCOLUMNS(
    erpPrjTimesheets, 
    &quot;tsPeriod&quot;,
    SELECTCOLUMNS(FILTER(cfgTimesheetMnth, erpPrjTimesheets[tsDate] &gt;= cfgTimesheetMnth[tsDateStart] &amp;&amp; erpPrjTimesheets[tsDate] &lt;= cfgTimesheetMnth[tsDateEnd]), &quot;tsPeriod&quot;, cfgTimesheetMnth[Period])
)

huangapple
  • 本文由 发表于 2023年8月5日 15:25:13
  • 转载请务必保留本文链接:https://go.coder-hub.com/76840564.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定