英文:
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
&& Task_Revisions[ChangedDate] < CurrentRowChangedDate
)
)
VAR CurrentCompletedWork = Task_Revisions[CompletedWork]
RETURN
IF ( PreviousCompletedWork <> 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], '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])
)
)
)
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论