Filtering a dataset by only a specific ID to create a cumulative total cost curve.

[Anonymous]

Hi there,

New to PowerBI and first attempt at creating a report.

I have merged a few tables together to create the table below.

MergedTaskResource

rtask_prop_id:ID

Title

ComplianceAssetId

resource_name

resource_id

override_sell_rate

role_group

role_name

cost_rate_salary

region

num_hours

rtask_prop_idId

resource_phase_fk:ID

resource_phase_fkId

rtask_idId

onshore

description

rtask_id:ID

full_loaded_cost

total

business unit

comments

ID.1

Modified

Created

GUID

rtask_prop_id

resource_phase_fk

rtask_id

Tasks.Id

Tasks.Title

Tasks.phase_fkId

Tasks.task_number

Tasks.start_date

Tasks.end_date

Tasks.task_name_calc

Tasks.AuthorId

Tasks.EditorId

DayDifference

AverageCost

15

null

null

NAME

0

Engineers

Engineer

Australia

100

8

4

4

6

onshore

Engineer

6

300

26674.25

null

null

15

27/06/2019 10:55:53 PM

27/06/2019 10:55:53 PM

6

new name PW

4

1

null

null

new name PW

18

18

null

0

I am interested in producing a cost over time graph based on proposal id which is the first field as the table has many rows from many different IDs. I have managed to create a manual cost over time graph for a specific ID, which I have hardcoded in a filter " MergedTaskResource[rtask_prop_id:ID]="3" ".

DailyCost = CALCULATE( SUM(MergedTaskResource[AverageCost]) , FILTER( MergedTaskResource, AND([Adjusted Dates]<=MergedTaskResource[Tasks.end_date], [Adjusted Dates]>=MergedTaskResource[Tasks.start_date])), FILTER( MergedTaskResource, MergedTaskResource[rtask_prop_id:ID]="3"))

I was wondering if there was a way to generalise the filtering so it worked for proposal IDs of any value that the user could select in the report rather than changing the code above.

Is there also a dynamic way to create the start and end dates for the line chart, as the current proposal actually finishes on 30 July 2019 and starts 30th June but the line chart goes from earlier June to September.

To give a bit more context, I have attached the steps I've used to create the cumulative costs above.

To do the following, two calculated columns DayDifference and average daily cost was added to the table MergedTaskResources.

A separate Calender table was used to do the daily calculation, in this same table I have added a few more calculated columns to create the table below.

DailyCost

DailyCost = CALCULATE( SUM(MergedTaskResource[AverageCost]) , FILTER( MergedTaskResource, AND([Adjusted Dates]<=MergedTaskResource[Tasks.end_date], [Adjusted Dates]>=MergedTaskResource[Tasks.start_date])), FILTER( MergedTaskResource, MergedTaskResource[rtask_prop_id:ID]="3"))

Adjusted Dates

Adjusted Dates = 'Dates'[Date] - (10/24)

Cumulative Total

Cumulative Total = CALCULATE( SUM(Dates[DailyCost]), 'Dates'[Adjusted Dates] <= EARLIER ('Dates'[Adjusted Dates]))

Thanks!

Here is some sample data,

Id	Title	ComplianceAssetId	resource_name	resource_id	override_sell_rate	role_group	role_name	cost_rate_salary	region	num_hours	rtask_prop_idId	resource_phase_fk:ID	resource_phase_fkId	rtask_idId	onshore	description	rtask_id:ID	rtask_prop_id:ID	full_loaded_cost	total	business unit	comments	ID.1	Modified	Created	GUID	rtask_prop_id	resource_phase_fk	rtask_id	Tasks.Id	Tasks.Title	Tasks.phase_fkId	Tasks.task_number	Tasks.start_date	Tasks.end_date	Tasks.task_name_calc	Tasks.AuthorId	Tasks.EditorId	DayDifference	AverageCost
156	null	null			150				Australia	0	3	27	27	27	onshore		27	3	98.4	0		test	156	30/07/2019 1:43:24 AM	23/07/2019 11:07:27 PM				27	Task One	27	1	30/06/2019 2:00:00 PM	8/07/2019 2:00:00 PM	Task One	13	13	9	0
157	null	null			0				Australia	1	3	27	27	27	onshore		27	3	100.08	110.088		null	157	24/07/2019 11:00:05 PM	23/07/2019 11:07:55 PM				27	Task One	27	1	30/06/2019 2:00:00 PM	8/07/2019 2:00:00 PM	Task One	13	13	9	12.232
158	null	null			0				Australia	5	3	27	27	27	onshore		27	3	98.4	541.2		null	158	24/07/2019 10:59:06 PM	24/07/2019 1:25:55 AM				27	Task One	27	1	30/06/2019 2:00:00 PM	8/07/2019 2:00:00 PM	Task One	13	13	9	60.13333
161	null	null			0				Australia	5	3	27	27	27	onshore		27	3	100.08	550.44		null	161	24/07/2019 10:59:07 PM	24/07/2019 1:40:06 AM				27	Task One	27	1	30/06/2019 2:00:00 PM	8/07/2019 2:00:00 PM	Task One	13	13	9	61.16
163	null	null			0				Australia	5	3	27	27	27	onshore		27	3	100.08	550.44		null	163	24/07/2019 10:59:09 PM	24/07/2019 2:20:47 AM				27	Task One	27	1	30/06/2019 2:00:00 PM	8/07/2019 2:00:00 PM	Task One	13	13	9	61.16
168	null	null			0				Australia	5	3	27	27	27	onshore		27	3	100.08	550.44		null	168	24/07/2019 10:59:11 PM	24/07/2019 4:46:51 AM				27	Task One	27	1	30/06/2019 2:00:00 PM	8/07/2019 2:00:00 PM	Task One	13	13	9	61.16
169	null	null			0				Australia	1	3	27	27	27	onshore		27	3	100.08	110.088		null	169	24/07/2019 11:00:06 PM	24/07/2019 4:50:20 AM				27	Task One	27	1	30/06/2019 2:00:00 PM	8/07/2019 2:00:00 PM	Task One	13	13	9	12.232
400	null	null			0				Australia	1	3	27	27	28	onshore		28	3	98.4	108.24		null	400	31/07/2019 3:59:32 AM	31/07/2019 3:59:32 AM				28	Task Two	27	2	8/07/2019 2:00:00 PM	14/07/2019 2:00:00 PM	Task Two	13	13	7	15.46286
170	null	null			0				Australia	1	3	28	28	29	onshore		29	3	100.08	110.088		null	170	24/07/2019 11:00:09 PM	24/07/2019 4:50:21 AM				29	Task One Phase Two	28	1	10/07/2019 2:00:00 PM	10/07/2019 2:00:00 PM	Task One Phase Two	13	13	1	110.088
401	null	null			0				Australia	1	3	27	27	135	onshore		135	3	98.4	108.24		null	401	31/07/2019 3:59:33 AM	31/07/2019 3:59:33 AM				135	Task 3	27	3	18/07/2019 2:00:00 PM	30/07/2019 2:00:00 PM	Task 3	13	13	13	8.326154
156	null	null			150				Australia	0	3	27	27	27	onshore		27	4	98.4	0		test	156	30/07/2019 1:43:24 AM	23/07/2019 11:07:27 PM				27	Task One	27	1	30/06/2019 2:00:00 PM	8/07/2019 2:00:00 PM	Task One	13	13	9	0
157	null	null			0				Australia	1	3	27	27	27	onshore		27	4	100.08	110.088		null	157	24/07/2019 11:00:05 PM	23/07/2019 11:07:55 PM				27	Task One	27	1	30/06/2019 2:00:00 PM	8/07/2019 2:00:00 PM	Task One	13	13	9	12.232
158	null	null			0				Australia	5	3	27	27	27	onshore		27	4	98.4	541.2		null	158	24/07/2019 10:59:06 PM	24/07/2019 1:25:55 AM				27	Task One	27	1	30/06/2019 2:00:00 PM	8/07/2019 2:00:00 PM	Task One	13	13	9	60.13333
161	null	null			0				Australia	5	3	27	27	27	onshore		27	4	100.08	550.44		null	161	24/07/2019 10:59:07 PM	24/07/2019 1:40:06 AM				27	Task One	27	1	30/06/2019 2:00:00 PM	8/07/2019 2:00:00 PM	Task One	13	13	9	61.16
163	null	null			0				Australia	5	3	27	27	27	onshore		27	4	100.08	550.44		null	163	24/07/2019 10:59:09 PM	24/07/2019 2:20:47 AM				27	Task One	27	1	30/06/2019 2:00:00 PM	8/07/2019 2:00:00 PM	Task One	13	13	9	61.16
168	null	null			0				Australia	5	3	27	27	27	onshore		27	4	100.08	550.44		null	168	24/07/2019 10:59:11 PM	24/07/2019 4:46:51 AM				27	Task One	27	1	30/06/2019 2:00:00 PM	8/07/2019 2:00:00 PM	Task One	13	13	9	61.16
169	null	null			0				Australia	1	3	27	27	27	onshore		27	4	100.08	110.088		null	169	24/07/2019 11:00:06 PM	24/07/2019 4:50:20 AM				27	Task One	27	1	30/06/2019 2:00:00 PM	8/07/2019 2:00:00 PM	Task One	13	13	9	12.232
400	null	null			0				Australia	1	3	27	27	28	onshore		28	4	98.4	108.24		null	400	31/07/2019 3:59:32 AM	31/07/2019 3:59:32 AM				28	Task Two	27	2	8/07/2019 2:00:00 PM	14/07/2019 2:00:00 PM	Task Two	13	13	7	15.46286
170	null	null			0				Australia	1	3	28	28	29	onshore		29	4	100.08	110.088		null	170	24/07/2019 11:00:09 PM	24/07/2019 4:50:21 AM				29	Task One Phase Two	28	1	10/07/2019 2:00:00 PM	10/07/2019 2:00:00 PM	Task One Phase Two	13	13	1	110.088
401	null	null			0				Australia	1	3	27	27	135	onshore		135	4	98.4	108.24		null	401	31/07/2019 3:59:33 AM	31/07/2019 3:59:33 AM				135	Task 3	27	3	18/07/2019 2:00:00 PM	30/07/2019 2:00:00 PM	Task 3	13	13	13	8.326154

[v-chuncz-msft · Accepted solution]

@Anonymous 

You may use ALLSELECTED as a CALCULATE modifier and add an appropriate measure to Visual level filters to adjust the time axis.