我可以使用DAX来确定在给定时间段内两个字段之间的差异吗?

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

Can I used DAX to determine the difference between two fields within a given time period

问题

I have a dataset that shows the total hours for every work item, with one row for each time the work item is updated (this is max 1 per day). Here is some sample data:

WorkItem CompletedWork ChangedDate
12392 4 01/04/23
12392 4.5 03/04/23
12394 1 02/04/23
12394 4 07/04/23
12530 6 06/05/23

I need a DAX expression showing the work item hours for each time period, with the intention of using this in a line chart with weekly increments on the x-axis showing work that has been completed in that time period.

I have tried writing this myself and cannot seem to get it working correctly. This is my current attempt, and I feel I am close - any help would be appreciated.

CompletedWorkDifference = 
SUMX (
    VALUES ( Task_Revisions[WorkItemId] ),
    VAR CurrentRowWorkItemId = Task_Revisions[WorkItemId]
    VAR CurrentRowChangedDate = Task_Revisions[ChangedDate]
    VAR PreviousCompletedWork =
        CALCULATE (
            LASTNONBLANK ( Task_Revisions[CompletedWork], 1 ),
            FILTER (
                ALL ( Task_Revisions ),
                Task_Revisions[WorkItemId] = CurrentRowWorkItemId
                    && Task_Revisions[ChangedDate] < CurrentRowChangedDate
            )
        )
    VAR CurrentCompletedWork = Task_Revisions[CompletedWork]
    RETURN
        IF ( PreviousCompletedWork <> BLANK (), CurrentCompletedWork - PreviousCompletedWork, BLANK () )
)

(Note: I've retained the original code as requested, but please let me know if you have any specific questions or need further assistance with it.)

英文:

I have a dataset that shows the total hours for every work item, with one row for each time the work item is updated (this is max 1 per day). Here is some sample data:

WorkItem CompletedWork ChangedDate
12392 4 01/04/23
12392 4.5 03/04/23
12394 1 02/04/23
12394 4 07/04/23
12530 6 06/05/23

I need a DAX expression showing the work item hours for each time period, with the intention of using this in a line chart with weekly increments on the x axis showing work that has been completed in that time period.

I have tried writing this myself and cannot seem to get it working correctly. This is my current attempt and I feel I am close - any help would be appreciated

    CompletedWorkDifference = 
SUMX (
    VALUES ( Task_Revisions[WorkItemId] ),
    VAR CurrentRowWorkItemId = Task_Revisions[WorkItemId]
    VAR CurrentRowChangedDate = Task_Revisions[ChangedDate]
    VAR PreviousCompletedWork =
        CALCULATE (
            LASTNONBLANK ( Task_Revisions[CompletedWork], 1 ),
            FILTER (
                ALL ( Task_Revisions ),
                Task_Revisions[WorkItemId] = CurrentRowWorkItemId
                    &amp;&amp; Task_Revisions[ChangedDate] &lt; CurrentRowChangedDate
            )
        )
    VAR CurrentCompletedWork = Task_Revisions[CompletedWork]
    RETURN
        IF ( PreviousCompletedWork &lt;&gt; BLANK (), CurrentCompletedWork - PreviousCompletedWork, BLANK () )
)

答案1

得分: 0

我能够使用以下的DAX公式解决这个问题,并将其与我为任务设置的日期表连接:

task_hours_by_time_period = 
SUMX(
    VALUES(task_revisions[WorkItemID]),
    CALCULATE(
        SUM(task_revisions[CompletedWork]),
        USERELATIONSHIP(task_revisions[ChangedDateSK], 'Task_Calendar'[DateSK]),
        FILTER(
            ALL('Task_Calendar'),
            'Task_Calendar'[Date] >= MIN('Task_Calendar'[Date]) &&
            'Task_Calendar'[Date] <= MAX('Task_Calendar'[Date])
        )
    ) - CALCULATE(
        SUM(task_revisions[CompletedWork]),
        USERELATIONSHIP(task_revisions[ChangedDateSK], 'Task_Calendar'[DateSK]),
        FILTER(
            ALL('Task_Calendar'),
            'Task_Calendar'[Date] < MIN('Task_Calendar'[Date])
        )
    )
)
英文:

I was able to solve this with the following DAX, and joining to a date table that I had setup for the tasks

task_hours_by_time_period = 
SUMX(
    VALUES(task_revisions[WorkItemID]),
    CALCULATE(
        SUM(task_revisions[CompletedWork]),
        USERELATIONSHIP(task_revisions[ChangedDateSK], &#39;Task_Calendar&#39;[DateSK]),
        FILTER(
            ALL(&#39;Task_Calendar&#39;),
            &#39;Task_Calendar&#39;[Date] &gt;= MIN(&#39;Task_Calendar&#39;[Date]) &amp;&amp;
            &#39;Task_Calendar&#39;[Date] &lt;= MAX(&#39;Task_Calendar&#39;[Date])
        )
    ) - CALCULATE(
        SUM(task_revisions[CompletedWork]),
        USERELATIONSHIP(task_revisions[ChangedDateSK], &#39;Task_Calendar&#39;[DateSK]),
        FILTER(
            ALL(&#39;Task_Calendar&#39;),
            &#39;Task_Calendar&#39;[Date] &lt; MIN(&#39;Task_Calendar&#39;[Date])
        )
    )
) 

huangapple
  • 本文由 发表于 2023年6月26日 13:52:59
  • 转载请务必保留本文链接:https://go.coder-hub.com/76553846.html
匿名

发表评论

匿名网友

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

确定